Order list by calc element, not saved in the database

[jo-ka]

Hello.
I need to order a list by a calc element that counts the number of days between a element date and today's date.

This element is not saved in the database, because its calculated everytime the list is loaded, and not when the record is edited or saved.

But I need to order the list by the number of days left, this calculation, and I'm not able to achieve it. If I put the order by on the list setup, it will fail because naturally there's no value on the field.

Is there a way to accomplish this, on the menu item for example?

 

[juuser]

You can update this calc field with list php-events plugin OnBeforeLoadData. So it will be updated in the database every time (before) the list is loaded. I cannot see any other reasonably easy way to do it.

 

[jo-ka]

Thanks @juuser .

I think the correct place is on OnPreLoadData, right? After your suggestion, I've checked the Wiki and I've found this example, which I think is the almost the same thing.
Let's giving a try...

http://www.fabrikar.com/forums/index.php?wiki/php-events-list-plugin/

 

[juuser]

Yup, OnPreLoadData is the correct term. And the code above should do what you need.

 

[jo-ka]

@juuser Close...

I fact with this code, the list value is changed, but not updated on the database, it's just a display condition, so, the order still doesn't work.
So, using this method, is there a way to update the database field, using the same method?

I'm thinkig of something like:

$data = $model->updateData(); and not getData();

 

[juuser]

OK, I think you need an update query for that, something like:

$mydb = JFactory::getDBO();
$mydb->setQuery("UPDATE your_table SET datediff_field = DATEDIFF(enddate_element, startdate_element) ");
$mydb->execute();

 

[jo-ka]

Thanks @juuser, I can try this, yes, but, wouldn't this method put some load on the list? I'm afraid that it will have a big impact on the list performance, when the list grows...

I'll post my results...

 

[juuser]

I have much more complicated scripts/queries in list plugins and with several thousand records performance drop is not noticeable at all. Depending on server performance, the above query should take just milliseconds to execute.

 

[jo-ka]

$mydb = JFactory::getDBO();
$mydb->setQuery("UPDATE your_table SET datediff_field = DATEDIFF(enddate_element, startdate_element) ");
$mydb->execute();

OK, I've made some tests with this option, but I've came across some issues:

1st - I have to add WHERE clause on the query, or the values are the same for every record. Something like:

 $mydb->setQuery("UPDATE your_table SET datediff_field = DATEDIFF(enddate_element, startdate_element) WHERE id = " . $row->your_table___id);

It works, but now I have an issue...
This only works when loading the first page. If the list is paginated, it doesn't work.

Any other ideas?

 

[juuser]

Do the values actually get inserted for all the records in database on list load? At least they should and the pagination should have no affect on this matter. Is the element you are updating a "field" element?

 

[jo-ka]

Do the values actually get inserted for all the records in database on list load? At least they should and the pagination should have no affect on this matter. Is the element you are updating a "field" element?

Yes, the values are actually getting inserted for all the records on the list.
But, it will just update the records on the second page, and so on, when you load that page. Can't really understand why.

And yes, the element being updated is a field element.

 

[juuser]

I'm not sure I'm getting the problem. Values are updated in database, but they are not visible in the list until you click another page in pagination links?

Is your list menu settings "Reset filters" set to "Yes"? If not, does changing that make a difference?

 

[jo-ka]

I'm not sure I'm getting the problem. Values are updated in database, but they are not visible in the list until you click another page in pagination links?

Is your list menu settings "Reset filters" set to "Yes"? If not, does changing that make a difference?

OK, so here's whar happens:

The list I have is pre-filtered on the menu item. So, for a list of almost 2000 records, there are shown just, more or less, 100 (those who has active status). Then, that menu item is configured to show 20 records per page, which gives me 5 pages.

When the page is loaded, those records on the first page are correctly updated on the database. The others just update when you go to the next page, and so on.

That's the behaviour I'm having right now. I have no filters active, other than the pre-filter on the menu item, but the changing this has no effect.

I don't know... I have no clue.

 

[juuser]

OK, I think I get the point. You have to probably do it with a loop, something like:

$mydb = JFactory::getDBO();

$mydb->setQuery("SELECT id, startdate_element, enddate_element FROM your_table");
$results = $mydb->loadObjectList();

foreach ($results as $result) {

$startdate = $result->startdate_element;
$enddate = $result->enddate_element;
$diff_in_days = $enddate->diff($startdate)->format("%a");

$mydb->setQuery("UPDATE your_table SET datediff_field = ".$mydb->Quote($diff_in_days)." WHERE id = ".$mydb->Quote($result->id));
$mydb->execute();

}

P.S. this exact code is not tested!

 

[jo-ka]

Hello @juuser . I have to test this... I'll bring the feedback later.

 

[henk]

For things like this I always prefer to use a view:

SELECT
your_table.id,
your_table.date,
DATEDIFF(curdate(),your_table.date) AS diff
FROM your_table

Then you can call this view: diff_table
Open the list of your_table and add a join to this view. Then sort data on the diff value of the view.
I think it's less complicated and it runs a lot faster.