Buy Now
Sign in

subquery

peppelaria , November 05 at 10:13
peppelaria
Hi, Max,
I need to perform this query:
SELECT​
​Customer.id AS `Customer.id`,​
​Customer.surname AS `Customer.surname`,​
​Customer.`name` AS `Customer.name`,​
​Customer.customer_status AS `Customer.customer_status`,​
​Customer.customer_substatus AS `Customer.customer_substatus`,​
(SELECT Count(`Event`.id)​
​FROM y1zly_events_participants_customers AS `Event`​
​WHERE​
​`Event`.customer_id = Customer.id AND​
​`Event`.id = 15 AND​
​`Event`.deleted IS NULL​
​) AS countEvent

​FROM​
​y1zly_chronoforms_data_customers AS Customer​
​WHERE​
​Customer.id IN ('12', '2689', '2963', '4473')​
As you can see, there is a subquery that returns a simple count of matching records.
How can I do this with a read_data action?
Thank you very mutch
healyhatman
If you add another model, there's a "SubQuery Join" option. I haven't figured out how it works yet but you seem to have a bit more of a grasp on MYSQL so you could give it a go.
If necessary you can just do the database query in PHP.
https://docs.joomla.org/Selecting_data_using_JDatabase
Web developer at SkySpider.com.au
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman
peppelaria
Hi healyhatman, thanks for your reply.
PHP code would be the extrema ratio.
I would like to use chronoforms read_data action instead, but at the moment I'trying to figure It out how subquery option works.
Please let me know if you have some idea about this