DISTINCT

7 posts by 3 authors in: Forums > CMS Builder
Last Post: May 21, 2015   (RSS)

By zaba - May 21, 2015 - edited: May 21, 2015

How do I modify this... I want to pull from the database the first 8 records from projects  but I only want one to be pulled for each client (the client can have a number of projects). The client field is called client.

Also the where clause is looking at the sector field, which is a list which can have multiple entries, so for example a project can be in sector 1 and sector 9, so i need to just pull the records where there is a match to $sector.

$sector=1;

  // load records from 'projects'
  list($projectsRecords, $projectsMetaData) = getRecords(array(
    'tableName'   => 'projects',
    'limit'       => '8',
    'loadUploads' => true,
    'allowSearch' => false,
    'where'       => "sector='$sector'",
  ));

By Dave - May 21, 2015

Hi zaba, 

You'd have to write a custom mysql query for that.  What's the total number of records in that section?  If it's not that many you could try just loading all the projects, looping over them, and only displaying the first record from each client in the foreach loop.  It's CPU/Memory time vs development time and would probably run pretty fast if you didn't have too many records.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

By gregThomas - May 21, 2015

Hey Zaba,

Is the client field a multi select field and does it link to another section in the CMS?

Thanks,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By zaba - May 21, 2015

No the client file is not multi select. in the editor it is pulled in as a single select list. with the value as 'num' (from the clients table) and label as 'the client name'

The sector is a multi select field in the editor this is pulled in as a multi select list. with the value as 'num' (from the sectors table) and label as 'the sector'.

So basically what I am trying to achieve is 

A. select the first 8 projects whose sector (this could contain more than 1 value) is $sector (which is a number passed to the page), 

B. Only display the projects from unique clients. 

By gregThomas - May 21, 2015 - edited: May 21, 2015

Hi Zaba, 

If the client field is a single select list field, then you can use the groupBy option in getRecords to only return one option for each client:

  // load records from 'projects'
  list($projectsRecords, $projectsMetaData) = getRecords(array(
    'tableName'   => 'projects',
    'limit'       => '8',
    'loadUploads' => true,
    'allowSearch' => false,
    'where'       => "`sector` LIKE '\t" . $sector . "\t'",
    'groupBy'     => 'client',
  ));

This will limit the items returned to one per client. 

This post by Chris explains how you can filter on a multi list item list for a section:

https://www.interactivetools.com/forum/forum-posts.php?postNum=2233515#post2233515

I've integrated this into the where statement in code above.

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By zaba - May 21, 2015 - edited: May 22, 2015

Note,

changed

  'where'       => "`sector` LIKE '\t" . $sector . "\t'",

TO

  'where'       => "`sector` LIKE '%\t" . $sector . "\t%'",

This checks multiple tab separated entries in the one field

Thats absolutely spot on...

Thanks Greg, you are a genius.

I would be stopping pulling out my hair around about now... if I had any!