Using a Join but getting duplicate records because of it... Want one record
3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 28, 2010 (RSS)
By internut - June 25, 2010
Hey all...
I'm a little stuck on how to go about this.
I have a "orders" table / section. and a "Updates" section which is used to add updates to an order (stages of the order process).
I'm trying to get a list of records from "orders" and joining the "updates" table to get the latest stage of the update.
Using code:
I get a record returned from "orders" as many times as there are related records in "updates". How can I get 1 record returned and just the most recent?
DISTINCT?
Thanks much!
I'm a little stuck on how to go about this.
I have a "orders" table / section. and a "Updates" section which is used to add updates to an order (stages of the order process).
I'm trying to get a list of records from "orders" and joining the "updates" table to get the latest stage of the update.
Using code:
// load records
list($ordersRecords, $ordersMetaData) = getRecords(array(
'tableName' => 'orders',
'leftJoin' => array(
'updates' => 'ON orders.num = updates.job',
'accounts' => 'ON accounts.num = updates.updatedByUserNum',
'whip_stages' => 'ON updates.stage = whip_stages.num',
),
'debugSql' => true,
'loadUploads' => '0',
'loadCreatedBy' => '0',
'where' => 'updates.job = orders.num AND archive = 0',
'limit' => '',
));I get a record returned from "orders" as many times as there are related records in "updates". How can I get 1 record returned and just the most recent?
DISTINCT?
Thanks much!
Re: [internut] Using a Join but getting duplicate records because of it... Want one record
By internut - June 25, 2010
Well a quick way around is I just cycle through the records and set a variable if its already went through a job number and if so not to show it again. Not the best way.
Now a new question is on record data from the join of "updates" its only pulling in:
[updates.job] => 533
[updates.stage] => 15
Shouldn't I be getting record created / updated variables returned?
Now a new question is on record data from the join of "updates" its only pulling in:
[updates.job] => 533
[updates.stage] => 15
Shouldn't I be getting record created / updated variables returned?
Re: [internut] Using a Join but getting duplicate records because of it... Want one record
By Chris - June 28, 2010
Hi internut,
I think your first issue is pretty tricky to solve. Here's [url http://stackoverflow.com/questions/881198/how-do-i-join-to-the-latest-record-in-the-table]one way to do it[/url] with raw SQL. I think the solution you came up with is probably just as good. This is one of those things that you would expect SQL to have no problem with but it ends up being ill-designed for.
Regarding your second issue: by default, special fields are not selected from joined records, but you can add them yourself. Try adding this to your getRecords() call:
I hope this helps! Please let me know if you have any questions.
I think your first issue is pretty tricky to solve. Here's [url http://stackoverflow.com/questions/881198/how-do-i-join-to-the-latest-record-in-the-table]one way to do it[/url] with raw SQL. I think the solution you came up with is probably just as good. This is one of those things that you would expect SQL to have no problem with but it ends up being ill-designed for.
Regarding your second issue: by default, special fields are not selected from joined records, but you can add them yourself. Try adding this to your getRecords() call:
'addSelectExpr' => 'updates.`createdDate` as `updates.createdDate`',I hope this helps! Please let me know if you have any questions.
All the best,
Chris
Chris