counting childs for each row

Lapointe

Need help please

I'd like to display count of childs from a child table for each row at mouseover:

in a post I read :

I want to set the title for each row on mouseover as 'Count of ... is : #v'

So I try to do somethig like :

$('#donnees').on( 'mouseover','tbody tr', function (e) {
    var t = table.row( this ).data();
    var r = $db->sql("SELECT annexes.Lot, Count(annexes.ID) AS Nb, types_annexes.Libelle 
        FROM types_annexes INNER JOIN annexes ON types_annexes.ID = annexes.Type 
        GROUP BY annexes.Lot, types_annexes.Libelle 
        HAVING (((annexes.Lot)="+t.lots.ID+"));")
    this.title = json_encode(r);
    } );    

and get at runtime :

can somebody tell me where is my mistake please ?

colin

Hi @Lapointe ,

It looks like you're mixing a server-side PHP script with the client side JavaScript - your line 3 doesn't belong in the browser.

Are you trying to count all open child rows, or just the child row for a specific row?

Cheers,

Colin

Lapointe

Hello @colin
You are right
I was working yesterday up to 18 hours and at 4AM I'm afraid my eyes where not in place...
A wake up this morning I had the same response... I was not in the correct part of application.

Thanks you

Response is for all rows I want to know how many childs or each types are dependant of the parent ID
The sql includes a group by option not allowed in fields::inst object (as I read)
Not in place but correct sql sentence to get information I need.
I read to it is possible to get data using view, but this way is less dynamic I think

Thanks for patience.

allan

I have a question before we attempt to solve this: is the data for the child rows already available on the client-side, or do you need to make an Ajax call to the server to get the child row data?

If the data is already on the client-side, then you can just take the .length of the array that you will be using for the child rows (e.g. table.row(x).data().children.length).

If you need to make an Ajax call, you'd also need to make an Ajax call to get the count. Doing that on hover would be quite slow. A better option perhaps would be to include the count in the data source for the rows when the main table is rendered (even if the child row data is not known, you'd know the number of child rows at least).

Allan

Lapointe

All data is server side (because multi users integrity)

I want to set the row 'title' value as (for example):

if row selected id is 18 (lots.ID) , get data from child table (join key is annexes.lot = lots.id)

Lot Count Libelle
18 1 Parking extérieur
18 3 Parking sous sol

and set title as

Parking extérieur : 1
Parking sous sol : 3

Lapointe

as explained for each parent row I can have 0 - x childs records.
If I create a view

CREATE VIEW lots_annexes AS 
SELECT annexes.Lot, Count(annexes.ID) AS Nb, types_annexes.Libelle
FROM types_annexes RIGHT JOIN annexes ON types_annexes.ID = annexes.Type
GROUP BY annexes.Lot, types_annexes.Libelle;

I will have more than one parent row displayed for each parent data and I need to set the group by for each field displayed (can't use SELECT annexes.* as need to group)

So how to get these records when on (select or mouseover or...) event fire ?

I don't understant a point in your answer... I thought I can get complete result including all needed datas in one sql call... Misunderstood ?

If you need to make an Ajax call, you'd also need to make an Ajax call to get the count.

allan

It looks like you have the count already available from the VIEW's data. So you should be able to do something like this:

$('#myTable').on('mouseover', 'tr', function () {
  var rowData = table.row(this).data();
  console.log('Child row count: '+ rowData.Nb);
});

You could then use the rowData.Nb however you want (e.g. showing a tooltip on hover).

Allan

Lapointe

Hi @allan
First of all, thanks for your help

I am newbee in JS (ajax) (and don't speak english very well to)
I never describe myself... I an 59 years old (60 in 30 days) and do software coding from windows 2 born, but never using js. I use sql, php, clipper, c, and vba
So my questions should seem strange, and I apologies for this

About your answer OK, I can know childs row count using a view , but but how does it help me?
How to, onmouseover get the data

Lot Count Libelle
18 1 Parking extérieur
18 3 Parking sous sol

and set title as

Parking extérieur : 1
Parking sous sol : 3

I think I need an ajax call to $db passing ID and foreach rows in result add text to this.title but don't know how to do and where place this ajax call...

cheers

allan

Happy birthday when it comes .

How to, onmouseover get the data

I showed how to do that above with the row().data() method. *edit Fixed link**

I think I need an ajax call to $db passing ID and foreach rows in result add text to this.title but don't know how to do and where place this ajax call...

As Colin noted above, its important that you keep your mental model of the PHP and client-side of things separate. For example you might make an Ajax request to a PHP script to get data you want to show in a modal.

Although it doesn't use a modal, this blog post might be of some help.

Allan

Lapointe

thanks @a>allan, @colin
in fact the first link you give me does not work (404)

The second link is what as was looking for...
Thanks all
Very nice project...

colin

Allan meant row().data() in the first link.

allan

Indeed I did. I've edited the post above to correct that should anyone else stumble across this.

Allan