SQL Query to exclude records with empty upload fields

5 posts by 3 authors in: Forums > CMS Builder
Last Post: March 24, 2022   (RSS)

By pgplast - March 20, 2022

I am building an application for which it is important to be able to limit my returned record set to only those that have values in an field of type 'upload'.

For example, I have a table that has a field called "cover_art" that is an upload field accepting images. Now once one has a recordset, it is simple enough to see if the cover_art upload field is empty. However, for my purpose, I need to limit the query from the outset, so that it never returns records with no cover_art images.

I have tried 

"where"  => "cover_art IS NOT NULL AND cover_art <> ''   "

but this returns the error: Unknown column 'cover_art' in 'where clause'

If I run a strsaight sql statement in the SQL console such as:

SELECT title FROM cmsb_tv_shows WHERE cover_art <>'' 

I get the same error

It seems that fields of type "upload" cannot be addressed in this way.

In order for my page to work I need to pull a random record from the table, but only from among those records that have an upload in the "cover_art" field.

How to do this???

Thanks.

pgplast

By daniel - March 21, 2022

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!

Daniel
Technical Lead
interactivetools.com

By pgplast - March 21, 2022

Thank you for the education, Daniel.

A big help!

By Codee - March 24, 2022

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!