• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Prefilter Query, can $q contain a query result set, not just a single value?

pastvne

Bruce Decker
I'm trying to use a very complex prefilter query in one of my apps

I've tested this query in PhpMyAdmin and it returns the correct results with multiple rowsets returned from the query:

SELECT `bpi_edoc_subscriptions`.`id`
FROM `bpi_edoc_subscriptions`
WHERE
CONCAT(`bpi_edoc_subscriptions`.`division_code` , '|', `bpi_edoc_subscriptions`.`customer_id`)
IN
(
SELECT CONCAT(`bpi_edoc_subscriptions`.`division_code` , '|', `bpi_edoc_subscriptions`.`customer_id`)
FROM `bpi_edoc_subscriptions`
WHERE
`bpi_edoc_subscriptions`.`status` = 'approved'
AND
`bpi_edoc_subscriptions`.`role`
IN
(SELECT `bpi_edoc_roles`.`id`
FROM `bpi_edoc_roles`
WHERE `bpi_edoc_roles`.`description` = 'client_admin'
)
AND
`bpi_edoc_subscriptions`.`subscriber_id` = '{$my->id}'
)


of course, in my PhpMyAdmin query tests I replace {$my->id} with a hard-coded value. This returns multiple rows as I expect with correct query results.

So, then I load that working query into the Prefilter Query of my list and then I add the following pre-filter:

WHERE
Field = id
Condition = IN
Value = {$q->id}
Type = Text

Instead of getting all rows in my list, I get just one, the first row that appears in my phpMyAdmin test query. I've tried working with eval and implode in PHP but I can't get a successful result. I'm worried that $q->id can only hold a single value and not an object or array of results. I was hoping that somehow a result set could be held in that object. I have some hope because I think that if $q->id was being overlaid, that it would contain the last value from the query, not the first value in the result set.

If this will not work, then how could I emulate the above example using normal prefilters? I've experimented but I find issues with using CONCAT() in eval types within prefilters in Fabrik 3.x for J2.5. Troester tested on the new Fabrik (which I cannot convert to quite yet) and prefilters do not have the issue I am experiencing.

Any advice appreciated...

Bruce Decker
Colorado
 
I am now wondering if the use of group_concat at the start of my query could cause a comma-delimited string to be returned from the pre-query? Is there an example of this and could this work to make MySQL do the work?
 
Well, I finally figured out a way to do this. I altered the prefilter query to use group_concat (can't believe I didn't think of that earlier) so that it now reads:

SELECT group_concat(`bpi_edoc_subscriptions`.`id`) as id
FROM `bpi_edoc_subscriptions`
WHERE
CONCAT(`bpi_edoc_subscriptions`.`division_code` , '|', `bpi_edoc_subscriptions`.`customer_id`)
IN
(
SELECT CONCAT(`bpi_edoc_subscriptions`.`division_code` , '|', `bpi_edoc_subscriptions`.`customer_id`)
FROM `bpi_edoc_subscriptions`
WHERE
`bpi_edoc_subscriptions`.`status` = 'approved'
AND
`bpi_edoc_subscriptions`.`role`
IN
(SELECT `bpi_edoc_roles`.`id`
FROM `bpi_edoc_roles`
WHERE `bpi_edoc_roles`.`description` = 'client_admin'
)
AND
`bpi_edoc_subscriptions`.`subscriber_id` = '{$my->id}'
)

This now returns the qualified records as a string of ids separated by commas. This is returned to the prefilter in $q->id but the trick was to add the 'as id' just after the group_concat. Without it, Fabrik becomes confused what to call the result set since it is now within a group_concat() and can no longer see the 'id' as part of the element name to use for it's object reference. I suppose, by that measure, that my 'as' label could have been whateverIChose so then my $q reference would be whatever I chose {$q->whateverIChose}

Once I used this query (with the proper as), then I did the normal best practice for prefilter query and added the following single pre-filter:

WHERE
Field = id
Condition = IN
Value = {$q->id}
Type = Text

The CONCAT() trick placing a pipe-char between the values of two fields allowed me to select unique rows based on values from two elements. In this case, it was the only efficient way to filter. I'm posting my solution here for documentation purposes because I think this technique is going to be quite useful for me and I suspect for others with complex filtering.

If this description requires more detail, let me know and I'll try to add it.

-BD
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top