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'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