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:

// 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 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:

'addSelectExpr' => 'updates.`createdDate` as `updates.createdDate`',

I hope this helps! Please let me know if you have any questions.
All the best,
Chris