Is this possible with Fabrik?

[woo10]

I have a list, Household, that has 2 joins, Person and Visit. Is it possible to only display visits that have occurred in the last 60 days? I tried to use a pre filter on Household, but if a Household has no visits they don't get displayed at all.

 

[cheesegrits]

I presume you are using a subquery in your pre-filter, and doing a ...

WHERE
Field: id (or whatever your PK is called)
Condition: IN
Value: SELECT something FROM something WHERE something
Type: query

If so, can you show us the query you are using. I suspect the problem is that if there are no visits, then the subquery returns no results, so the main query ends up having "AND id IN ()", which won't yield any results (and is probably erroring out).

Anyway ... let us know exactly how you are doing this filter.

BTW - another potential gotcha, which we just discovered, is that if you pre-filter to remove related rows (rather than main table rows), then edit a record and save it, the related rows which were filtered out will get deleted. Because pre-filters get applied to all access of a table, so the filtered out rows won't show up in the form ... and when you submit a form with related data, part of our form processing is that we delete any related data in the database for that form which is not submitted with the form (on the assumption that the user deleted it on the form).

We will be working to resolve that gotcha, but for now, its a limitation. If you allow row editing on a list with pre-filters, you can't really use pre-filters to hide related data. Only main table rows.

-- hugh

 

[woo10]

That "gotcha" could be a problem for this. Is there an easy way to just limit the number of rows of related data returned? In other words only return the last 5 visits instead?

 

[woo10]

I thought about this overnight. I think my best option is to add a "calc' element that displays the number of visits for the last 60 days. This is my first time using the calc plugin and I am not getting anywhere with the documentation. I pieced together this MySQL query

SELECT
tri_visit.visit_type,
tri_visit.date_time
FROM
tri_visit
WHERE
tri_visit.visit_type = 4 AND
tri_visit.fk_household_ID = {tri_household___id_raw} AND
DATEDIFF(CURDATE(), tri_visit.date_time) <60

I can get this query to work in Navicat (by replacing {tri_household___id_raw} with a row #). Can someone help with how to count the records returned and display it back in the list?

 

[woo10]

Also tried this

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select("COUNT (*) AS total")->from("tri_visit")->where("tri_visit.visit_type = '4' AND tri_visit.fk_household_ID = {tri_household___id_raw} AND  DATEDIFF(CURDATE(),'tri_visit.date_time'))" <60;
$db->setQuery($query);
return $db->loadResult();

Still no joy. Can someone at least let me know if I am headed in the right direction?

 

[woo10]

Solved with this

$db = JFactory::getDbo();
$tdate = date("Y-m-d H:i:s");
$vdate = 'tri_visit.date_time_raw';
 
$query = "SELECT\n"
    . "  Count(*) AS total,\n"
    . "    tri_visit.date_time,\n"
    . "    tri_visit.visit_type,\n"
    . "    tri_visit.fk_household_ID\n"
    . "FROM\n"
    . "    tri_visit\n"
    . "WHERE\n"
    . "    DATEDIFF(NOW(), tri_visit.date_time) < 60\n"
    . "AND tri_visit.visit_type = 4\n"
    . "AND tri_visit.fk_household_ID = {tri_household___id}";
   
$db->setQuery($query);
return $db->loadResult();

Frustrated with PHP.

 

[cheesegrits]

The only thing I would modify there is to quote the '{tri_household___id}', so you don't get a MySQL error if that element is empty (which it will be when, for instance, creating a new form).

It's also ALWAYS good practice to quote anything you get from a form input:

http://xkcd.com/327/

So in this case, somewhere at the top ...

$household_id = $db->quote('{tri_household___id}');

... and then use that in the query.

-- hugh