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!
interactivetools.com
By zaba - May 21, 2015
Ok, could you give an example of that, theres not too many records.
Also do you know how I can achieve the second part to my question?
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
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
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!