Need to create a list of items from multiple resource sets.

69 Posts

jeffsydor Reply #1, 5 years, 7 months ago

Not sure exactly how to describe this. Please ask for any clarification.

I have 3 resource groups that I’m trying to cross reference to end up with a single list from the group.

People
Locations
Services

Both Locations and Services use random unique ID strings generated in the link_attributes field whereas People does not. I use template variables to assign locations and services to each person and pdoResources to generate lists of said people to show lists of them under both services and locations. Sample below of list of people under locations.

[[pdoResources?
  &parents=`186,187,188`
  &depth=`1`
  &tpl =`personTpl`
  &sortby=`{"AttorneyLastName":"ASC"}`
  &includeTVs=`AttorneyServicesAndIndustries,AttorneyPhoto, AttorneyPrefix, AttorneySuffix, AttorneyTitle, AttorneyMiddleName, AttorneyLastName , AttorneyFirstName, AttorneyBusinessEmail,AttorneyBusinessPhone`
  &tvPrefix=``
  &tvFilters=`AttorneyOffices==%[[*link_attributes]]%` 
  &limit=`1000`
  &totalVar=`totalPeoples`
]]

I need to show a list of all the services only for people at a specific location. I configured a search query within MySQL to pull each person and search for the pagetitles of the corresponding link_attribute and build an alias column in the table with the new string. In the sample below, 160 is the resource id for the San Diego office. The output is displayed in the attached thumbnail. I would need to make this dynamic also based on the resource that is using the template. But that's easy.

SELECT
    attorney.id AS id,
    tfirstnamevalue.value AS firstname,
    tlastnamevalue.value AS lastname,
    SUBSTRING_INDEX(location_vals.value, "||", 1) location_val,
    (
        SELECT id
        FROM modx_site_content
        WHERE link_attributes = location_val
        LIMIT 1
    ) location_id,
    (
        SELECT pagetitle
        FROM modx_site_content
        WHERE link_attributes = location_val
        LIMIT 1
    ) location_name,
    SUBSTRING(mainservice.value, 1, 2000) service_ids,
    (
    	SELECT GROUP_CONCAT(pagetitle)
        FROM modx_site_content services_table
        WHERE template IN (12,28) AND LOCATE(link_attributes, service_ids)
    ) all_services
FROM
    modx_site_content attorney
LEFT JOIN
	modx_site_tmplvar_contentvalues tfirstnamevalue ON
    tfirstnamevalue.contentid = attorney.id AND tfirstnamevalue.tmplvarid = 68
LEFT JOIN
	modx_site_tmplvar_contentvalues tlastnamevalue ON
    tlastnamevalue.contentid = attorney.id AND tlastnamevalue.tmplvarid = 69
LEFT JOIN
	modx_site_tmplvar_contentvalues mainservice ON
    mainservice.contentid = attorney.id AND mainservice.tmplvarid = 81
LEFT JOIN
	modx_site_tmplvar_contentvalues location_vals ON
    location_vals.contentid = attorney.id AND location_vals.tmplvarid = 82
WHERE
	attorney.deleted = 0 AND attorney.published = 1 AND attorney.template IN(16,51) AND (SELECT id FROM modx_site_content WHERE link_attributes = (SUBSTRING_INDEX(location_vals.value, "||", 1)) LIMIT 1) = 160
ORDER BY
	lastname ASC

I’m stuck at this point. I’m not sure how to build this list in modx, pull the content from the alias column and remove all duplicates to create a dynamic list that can be used on each location page.

I was thinking that if I could dump those results into one placeholder or something, I could use THAT as my resource that pdoResources or something refers to as its source and just filters out duplicates.

Any help would be appreciated.

Thank you!

This question has been answered by jeffsydor. See the first response.

1,154 Posts

nuan88 Reply #2, 5 years, 7 months ago

Seems to me you would first scan the db for the relevant people based on the chosen location.

Then, I think you will want to take the array (which would be the employee id numbers) and then gather the attributes (Services) for each,

For each ([name] as Name), get...[attributes]

then just dump it out into a tpl I guess

[ed. note: nuan88 last edited this post 5 years, 7 months ago.]

69 Posts

jeffsydor Reply #3, 5 years, 7 months ago

Unfortunately, I don't even know where to start with what I'm looking for to do that.

1,154 Posts

nuan88 Reply #4, 5 years, 7 months ago

Ok let me see if I've got this correct. I think I just changed my mind.

Here, you just need to insert the dynamic office id number into your script that you posted, right? You said it outputs properly.

If so, you just need to have a form save the value, then call that value in the script. I was going about it all wrong.

A simple form can save a value, then you can call that value dynamically in your script.

The script could be a posthook from the form, if there is no page refresh I believe the value is there.

Alternatively you could save that to a tv and get the tv value in the form.

Something like [[+value]] should be available to your code once set without page refresh [ed. note: nuan88 last edited this post 5 years, 7 months ago.]

69 Posts

jeffsydor Reply #5, 5 years, 7 months ago

I'm hoping that this is even simpler than that.

When I run this code:

[[pdoResources?
    &parents=`186,187,188`
    &depth=`0`
    &sortby=`{"pagetitle":"ASC"}`
    &select=`{"modResource":"id,pagetitle,uri,parent"}`
    &includeTVs=`AttorneyServicesAndIndustries`
    &tvPrefix=``
    &tvFilters=`AttorneyOffices==%[[*link_attributes]]%`
    &limit=`100`
    &totalVar=`totalCityPeople`
    &tpl=`@INLINE [[+AttorneyServicesAndIndustries]]`
    &toPlaceholder=`CityServs`
    &showLog=`1`
]]

The MySQL output is this:

SELECT
    `modResource`.`id`,
    `modResource`.`pagetitle`,
    `modResource`.`uri`,
    `modResource`.`parent`,
    IFNULL(`TVattorneyoffices`.`value`, '') AS `AttorneyOffices`,
    IFNULL(`TVattorneyservicesandindustries`.`value`,'') AS `AttorneyServicesAndIndustries`
FROM
    `modx_site_content` AS `modResource`
LEFT JOIN `modx_site_tmplvar_contentvalues` `TVattorneyoffices` ON
    `TVattorneyoffices`.`contentid` = `modResource`.`id` AND `TVattorneyoffices`.`tmplvarid` = 82
LEFT JOIN `modx_site_tmplvar_contentvalues` `TVattorneyservicesandindustries` ON
    `TVattorneyservicesandindustries`.`contentid` = `modResource`.`id` AND `TVattorneyservicesandindustries`.`tmplvarid` = 81
WHERE
    (
        `TVattorneyoffices`.`value` LIKE '%C0D62CBD-45FC-4392-936B-7165E85084C3%' AND `modResource`.`parent` IN(186, 187, 188) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0
    )
ORDER BY
    modResource.pagetitle ASC
LIMIT 100

See the First attachment for this SQL result.

I do know how to translate the AttorneyServicesAndIndustries selection to their corresponding MODX IDs with the following subquery for AttSI_IDs in MySQL using LOCATE:

SELECT
    `modResource`.`id`,
    `modResource`.`pagetitle`,
    `modResource`.`uri`,
    `modResource`.`parent`,
    IFNULL(`TVattorneyoffices`.`value`, '') AS `AttorneyOffices`,
    IFNULL(`TVattorneyservicesandindustries`.`value`,'') AS `AttorneyServicesAndIndustries`,
    (
      SELECT
        GROUP_CONCAT(id)
      FROM
        modx_site_content services_table
      WHERE
        template IN(12, 28, 54) AND LOCATE(link_attributes, AttorneyServicesAndIndustries)
    ) AttSI_IDs
FROM
    `modx_site_content` AS `modResource`
LEFT JOIN `modx_site_tmplvar_contentvalues` `TVattorneyoffices` ON
    `TVattorneyoffices`.`contentid` = `modResource`.`id` AND `TVattorneyoffices`.`tmplvarid` = 82
LEFT JOIN `modx_site_tmplvar_contentvalues` `TVattorneyservicesandindustries` ON
    `TVattorneyservicesandindustries`.`contentid` = `modResource`.`id` AND `TVattorneyservicesandindustries`.`tmplvarid` = 81
WHERE
    (
        `TVattorneyoffices`.`value` LIKE '%C0D62CBD-45FC-4392-936B-7165E85084C3%' AND `modResource`.`parent` IN(186, 187, 188) AND `modResource`.`published` = 1 AND `modResource`.`deleted` = 0
    )
ORDER BY
    modResource.pagetitle ASC
LIMIT 100

This returns the second attachment.

My question right now is, is there a way to instruct pdoResources to insert a subquery following the AttorneyServicesAndIndustries call in SELECT?

If I can do this, my following pdoResources query would be far easier because I can just use the output of this in my &resources=`` parameter. Even if there are duplicates, it wouldn't list them multiple times in the output.

discuss.answer

69 Posts

jeffsydor Reply #6, 5 years, 7 months ago

So it turns out that this was working all along. The only thing was that for whatever reason, the @INLINE tpl wasn't working. When I put the exact same code into a chunk and referenced THAT as the template, it worked.

1,154 Posts

nuan88 Reply #7, 5 years, 7 months ago

Wow great!

Answered Need to create a list of items from multiple resource sets.