Row numbering on the fly

[dimoss]

Hi

MySQL does not have any system function like SQL Server?s row_number () to generate the row number for each row. However it is possible to use variable in a query to get the same result like:

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number FROM dbtale;

My question is how i could implement this in a list using a calc element or something else (ex. display element).

Thanks!

 

[dimoss]

Hi again

I tried this but I get number 1 in all rows. I am sure that I miss something but what?

$db = JFactory::getDBO();
$query = "set @row_number:=0";
$db->setQuery( $query );
$db->execute();
$query = "SELECT @row_number:=@row_number+1 AS row_number FROM fab_countries";
$db->setQuery( $query );
$no = $db->loadResult();
return $no;

If I use $no = $db->loadColumn(); I get the result but it counts 1-xxx for every single row in the table!

Any ideas?

 

[cheesegrits]

You can't do it on a calc, as that runs a separate query for the calc in each row. It would have to done in the main getData() query that selects all the rows being displayed. And that's not possible with Fabrik as-is, we don't have plugin hooks available in all the places you'd need.

The only way I can think of doing it would be in a custom template, where the rows are actually rendered.

-- hugh

 

[dimoss]

Hi Hugh

Thanks for yr time to reply to my question!
As I have no idea what to change in a custom bootstrap template to add this functionality I would appreciate yr help...

 

[cheesegrits]

It kind of depends what you are trying to do.

Do you need this purely for display purposes, or do you need your numbering stored in the data, or used anywhere else other than viewing the list?

-- hugh

 

[dimoss]

Hi Hugh

It's only for displaying in the list purposes. Numbering the records. Nothing else. Nothing stored.
Thanks

 

[cheesegrits]

And do you need to take account of pagination, so if you paginate from page 1 (with 10 rows) to page 2, it numbers 11 through 20?

-- hugh

 

[dimoss]

Yes.correct.

 

[cheesegrits]

That makes it somewhat more difficult.

I remember answering this same question, and providing some template code to do it, a number of years ago. Have you tried searching the forums?

-- hugh

 

[dimoss]

Hi Hugh

I looked for row numbering but I couldn't find something relevant..:-(
Maybe I missed something?

 

[cheesegrits]

OK, I found an easier way to do it.

Add a field element for your row num. You can set it to hidden, but display in list.

Add a php_events plugin to your list, and in the onLoadData box, put:

$rownum = $model->limitStart;
$data = $args[0]->data;
foreach ($data as $group) {
   foreach ($group as $row) {
      $rownum++;
      $row->fab_list_plugin_test___row_num = $rownum;
      $row->fab_list_plugin_test___row_num_raw = $rownum;
   }
}

Replace fab_list_plugin_test___row_num with your full element name.

-- hugh

 

[dimoss]

Hi Hugh

I did what u suggested but it doesnt work.
http://entries.alfaweb.gr/index.php?option=com_fabrik&view=list&listid=11&Itemid=122
Select the first tournament and click GO
It doesn't return the row numbering.
Is this because of the filters? I guess not because even if I remove the require filter it still doesn't count.

 

[cheesegrits]

Works for me on the test I set up.

Paste your code.

-- hugh

 

[dimoss]

Here is the code is list php_events plugin

$rownum = $model->limitStart;
$data = $args[0]->data;
foreach ($data as $group) {
   foreach ($group as $row) {
      $rownum++;
      $row->fab_doubles___no = $rownum;
      $row->fab_doubles___no_raw = $rownum;
   }
}

 

[cheesegrits]

And you are sure you have this in the onLoadData box, not onPreLoadData?

If so, try putting some debug in.

var_dump($data);exit;

... as the third line (after you assign $data).

-- hugh

 

[dimoss]

Hi Hugh

As you see in attached I added the code in the onLoadData box.
I debug it and the result was 'NULL'

 

[dimoss]

I don't know if this help but looking my error log I see this error:
PHP Warning: Invalid argument supplied for foreach() in /home/xxxxx/public_html/entries/plugins/fabrik_list/php_events/php_events.php(232) : eval()'d code on line 3

By the way...I use J3.7.5 and Github code from July 2017.

 

[dimoss]

Hi again!

It seems that the problem was my old fabrik version.
I updated to the latest Github and it work like a charm!
Thanks Hugh!..

 

[cheesegrits]

Oh yeah, oops, I forgot that onLoadData needed a small change I'd committed to github fairly recently, shortly before the 3.8 release. Sorry about that.

-- hugh