SQL Query to exclude records with empty upload fields

Hi pgplast,

As you've noticed, upload fields are not handled directly in the table, so can't be queried like this. Essentially each uploaded image in the field is stored as its own record in a separate uploads table, so instead of checking for "empty field" we can look for "has any records in uploads table."

To do this, you can use a left join on the "uploads" table and match it up with the record num, then narrow it down to the correct table name and field name. Something like this:

  list($records, $metaData) = getRecords(array(
    'tableName'   => 'tv_shows',
    'leftJoin' => [
      'uploads' => 'ON tv_shows.num = uploads.recordNum'
    ],
    'where' => "uploads.tableName = 'tv_shows' AND uploads.fieldName = 'cover_art'",
    'groupBy' => 'tv_shows.num',
  ));

If you're ever curious, you can visit "/cmsb/admin.php?menu=uploads" in your CMSB admin to view the records in the uploads table, which may give some better context around how the data is structured and how to access it.

Let me know if you're able to get this to work, or if you have any other questions.

Thanks!

Thank you for the education, Daniel.

A big help!

Hello pgplast,

I see Daniel already started the education on CMSBs data fields storage vs. image fields storage.  I have a thought, or suggestion, in considering your challenge.  A few years back I had a very similar limitation on a client's site (not wanting to show records that do not have an uploaded image). In my case, my client wanted random "featured records" to appear just below the header and rotate through them automatically...preferably WITHOUT using javascript.  Making the function happen wasn't overly difficult but it was unacceptable to show any records that did not have an image.  Originally to help solve this I put a checkbox in the section records entitled "Feature".  If the record had the "Featured" field checked, then the record would rotate in with the other displaying featured records.  At first it was no problem but then the client wanted all newly added records to have the "Feature" box checked by default. Not a problem to do that, either...BUT moving forward the client did not ensure that all of his newly added records were complete with photo uploads.  Suddenly there were blank spaces rotating through the Featured records display.  The client did not want to have the function reverted back so a very easy, inexpensive, solution was this: I created a default graphic that would appear for the records that had no uploaded images. The client was more than happy with this middle-road solution.

Then, in the very large, majorly populated general gallery section for that particular website, the client requested for a default graphic image to appear (icon_nophoto.jpg) if no image was uploaded for that record.  Here's a snippet from the gallery page that shows this new default:

<a href="<?php echo $record['_link'] ?>" border="0">
   <?php foreach ($record['graphic'] as $upload): ?>
      <?php if ($upload['hasThumbnail']): ?>
         <img src="<?php echo $upload['thumbUrlPath'] ?>" border="0" width="<?php echo $upload['thumbWidth'] ?>" height="<?php echo $upload['thumbHeight'] ?>" alt="" ><br>
         <?php break; ?>
      <?php elseif ($upload['isImage']): ?>
          <img src="<?php echo $upload['urlPath'] ?>" border="0" width="<?php echo $upload['width'] ?>" height="<?php echo $upload['height'] ?>" alt="" ><br>
      <?php else: ?>
          <a href="<?php echo $upload['urlPath'] ?>"></a><br>
      <?php endif ?>
   <?php endforeach ?>
   <?php if (!$record['graphic']): ?>
      <img src="/images/icon_nophoto.jpg" border="0"><br>
   <?php endif ?>
</a>

I realize this is not the specific question you had asked but your scenario sounded a bit like the one I just described so I am throwing this into the ring in case it may help you, or another CMSB user who may need it. 

Good luck!