Gallery - Uploads Table

Hi Perch,

There might be an idea in this where clause that you can use to sort it out.

'where' => " createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND membership_level LIKE '%2%') ) AND  updatedDate > '$listing_age' OR (createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND (membership_level LIKE '%1%'  OR membership_level LIKE '%3%' OR membership_level LIKE '%4%' OR membership_level LIKE '%5%' OR membership_level LIKE '%6%')) ) ) ",

Best,

Jerry Kornbluth

Hi, Jerry -

Thanks for your input. I never knew we could do so much with the "Where" clause! However, the uploads table does not have a createdByUserNum column - so I need to think of a different approach.

:0/

Perch

Hi Perch,

It would probably be easier to access the gallery table and add you're where statement there. Then loop through the gallery uploads:

  // load record from 'accounts'
  list($galleries, $galleriesMeta) = getRecords(array(
    'tableName'   => 'gallery',
    'where'       => "`createdByUserNum` = '1'",
    'loadUploads' => true,
    'allowSearch' => false,
  ));

  foreach($galleries as $gallery){
    foreach($gallery['upload_field_name'] as $upload){
      echo "<img src='{$upload['urlPath']}' alt='{$upload['info1']}' />";
    }
  }

If you'd rather access the uploads table directly, you could use a leftJoin to link the recordNum field to the gallery table:

  // load record from 'accounts'
  list($galleries, $galleriesMeta) = getRecords(array(
    'tableName'   => 'uploads',
    'where' => " tableName = 'gallery' ",
    'loadUploads' => false,
    'allowSearch' => false,
    'leftJoin'    => array('gallery' => 'recordNum'),
  ));


Although as getRecords wasn't designed to access the uploads table directly, I'm not 100% sure if this method will work.

Thanks,

Greg

Hi, Greg -

Thanks for this. I've never used the leftJoin in a getRecords call before - but I'm now realising just how useful it might be!

In this instance, however, it doesn't quite work. The join pulls in all of the columns from the gallery table - except: createdDate, createdByUserNum, updatedDate and updatedByUserNum.

Can you explain why this happens (or doesn't happen)?

Thanks,

:0)

Perchpole

Hey Perch,

This happens because the leftJoin is designed to only return fields with a type, it will only return the following field types:

• textfield
• textbox
• wysiwyg
• date
• list
• checkbox

But as the createdDate, updatedDate, etc are internal fields, they don't have a type set on them. 

Even though you can't view these fields in the returned data, you can still filter on them in your where statement.

Thanks!

Greg

Ahhh... Yes!

Thanks for explaining.

:0)

Perch