Buy Now
Sign in

subquery

peppelaria , November 05 2018, 10:13
peppelaria 159
November 05 2018, 10:13 #381262
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 7
November 05 2018, 10:23 #381263
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 DO NOT work for ChronoEngine!
Donations: paypal.me/healyhatman
Professional Work: kamron@skyspider.com.au
Custom CF / CC Plugins and Extensions: skyspider.com.au/store
peppelaria 159
November 05 2018, 11:01 #381264
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