Roi,
I loved using Playa on 1.6.8. I haven’t implemented it yet on 2 (Just from lack of need thus far.)
This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.
The active forums are here.
April 08, 2011 7:02pm
Subscribe [4]#16 / Apr 12, 2011 12:33pm
Roi,
I loved using Playa on 1.6.8. I haven’t implemented it yet on 2 (Just from lack of need thus far.)
#17 / Apr 12, 2011 1:04pm
Roi,
Being that you have been down this road before, I have a question for you. (Or anyone for that matter) 😉
What is the best way to only include the most recent entry in your query? I can get all of my information, but I noticed some of the entries looked incorrect. When I looked at it in detail, I noticed that the query was looking through all records (as it should) the way I have it now.
I need to write this to where it only looks at the most recent records that are live/open.
Any direction would be appreciated.
Thanks,
#18 / Apr 12, 2011 2:19pm
I guess I could elaborate a bit on the scenario. My basic query runs fine and only pulls the latest “version” or entries. The problem comes when I throw the ‘channel_data’ table in the mix. Once this happens, it pulls every entry with the custom fields that exist.
So for example, using one of EE’s basic queries from the CP (assuming the DB prefix is ‘exp’):
SELECT exp_channel_titles.entry_id, exp_channel_titles.channel_id, exp_channel_titles.title, exp_channel_titles.author_id, exp_channel_titles.status, exp_channel_titles.entry_date, exp_channel_titles.dst_enabled, exp_channel_titles.comment_total, exp_channels.live_look_template, exp_members.username, exp_members.email, exp_members.screen_name
FROM exp_channel_titles
LEFT JOIN exp_channels ON exp_channel_titles.channel_id = exp_channels.channel_id
LEFT JOIN exp_members ON exp_members.member_id = exp_channel_titles.author_id
WHERE exp_channel_titles.entry_id IN (SELECT DISTINCT exp_channel_titles.entry_id FROM exp_channel_titles WHERE exp_channel_titles.site_id = '6' ORDER BY entry_date desc)
ORDER BY entry_date descThis pulls up the entries as needed. If there are two entries, regardless of how many revisions or changes have been made, 2 show up.
BUT… :
SELECT exp_channel_titles.entry_id, exp_channel_titles.channel_id, exp_channel_titles.title, exp_channel_titles.author_id, exp_channel_titles.status, exp_channel_titles.entry_date, exp_channel_titles.dst_enabled, exp_channel_titles.comment_total, exp_channels.live_look_template, exp_members.username, exp_members.email, exp_members.screen_name, exp_channel_data.field_id_1 AS 'Something', exp_channel_data.field_id_2 AS 'Something Else'
FROM exp_channel_titles
LEFT JOIN exp_channels ON exp_channel_titles.channel_id = exp_channels.channel_id
LEFT JOIN exp_members ON exp_members.member_id = exp_channel_titles.author_id
LEFT JOIN exp_channel_data ON exp_channel_titles.channel_id = exp_channel_data.channel_id
WHERE exp_channel_titles.entry_id IN (SELECT DISTINCT exp_channel_titles.entry_id FROM exp_channel_titles WHERE exp_channel_titles.site_id = '6' ORDER BY entry_date desc)
ORDER BY entry_date descThis, on the other hand (assuming the same as above), pulls up multiple entries for each ID. I have tried DISTINCT and other approaches, but not succeeded. I know this is probably a simple thing that I am just missing, but I haven’t seen it yet.
Thanks again…
#19 / Apr 12, 2011 2:43pm
OOPS!!! Stupid mistake…
Again assuming the setup is the same as the above post…
SELECT exp_channel_titles.entry_id, exp_channel_titles.channel_id, exp_channel_titles.title, exp_channel_titles.author_id, exp_channel_titles.status, exp_channel_titles.entry_date, exp_channel_titles.dst_enabled, exp_channel_titles.comment_total, exp_channels.live_look_template, exp_members.username, exp_members.email, exp_members.screen_name, exp_channel_data.field_id_1 AS 'Something', exp_channel_data.field_id_2 AS 'Something Else'
FROM exp_channel_titles
*****
LEFT JOIN exp_channel_data ON exp_channel_titles.entry_id = exp_channel_data.entry_id
*****
LEFT JOIN exp_channels ON exp_channel_titles.channel_id = exp_channels.channel_id
LEFT JOIN exp_members ON exp_members.member_id = exp_channel_titles.author_id
WHERE exp_channel_titles.entry_id IN (SELECT DISTINCT exp_channel_titles.entry_id FROM exp_channel_titles WHERE exp_channel_titles.site_id = '6' ORDER BY entry_date desc)***** Notice the change in the first Join.
I was running against the channel not the entry… :red: