On our attorney pages we have a section for “related services & industries” and a list of the areas each person practices. For example:
http://www.bipc.com/christopher-thorn. The field that lists all their practice areas in the CMS is set up to target the first instance and set that as their “primary” practice. This is what is used on their “cards” in the search results.
Basically here’s an example of the problem: Using Chris Thorn’s bio above, you can see his primary practice is Immigration but he is also a member of the Labor & Employment practice. If you search for Labor & Employment (ignore the dynamic search…just hit enter), you’ll see everyone that is primarily in the Labor & Employment section is displayed. But people like Chris don’t because that phrase is not in his bio.
Using that and looking at the sphinx.config file, it looks like only a few things are indexed in each record; the primary service (mainservice), the name, the template, the location and the content (biography) area. I’m unsure how to modify the mainservice to index ALL the variables in that section not just the first.
My experience with MySQL is very limited, so I only have a basic understanding of what this even says. I apologize if my explanations don’t make sense at first.
Here is the sql_query. I’ve broken it apart to read it better:
sql_query = SELECT
attorney.id AS id,
attorney.pagetitle AS pagetitle,
attorney.content AS content,
attorney.template AS template,
location_ids.value as locations,
tlastnamevalue.value AS lastname,
SUBSTRING(mainservice.value, 1,36) service_id,
(SELECT pagetitle FROM modx_site_content WHERE link_attributes = service_id LIMIT 1) main_service,
(SELECT GROUP_CONCAT( pagetitle )
FROM modx_site_content location_table
WHERE template =22
AND LOCATE( link_attributes, locations )
) location_names
FROM modx_site_content attorney
LEFT JOIN modx_site_tmplvar_contentvalues tlastnamevalue
ON tlastnamevalue.contentid = attorney.id
AND tlastnamevalue.tmplvarid = 69
LEFT JOIN modx_site_tmplvar_contentvalues location_ids
ON location_ids.contentid = attorney.id
AND location_ids.tmplvarid = 82
LEFT JOIN modx_site_tmplvar_contentvalues mainservice
ON mainservice.contentid = attorney.id
AND mainservice.tmplvarid = 81
WHERE attorney.deleted = 0 AND attorney.published=1
AND attorney.template IN (16,49)
ORDER BY lastname ASC
I may be wrong, but the way I read this was that this SUBSTRING was selecting all the values from “mainservice” starting from the first record, the first 36 characters of each record and putting those results into “service_id”, and then only indexing that first result (LIMIT 1).
Also, further down is a LEFT JOIN command that is defining mainservice as the items from the Template Variable #81, which is the id for Attorney Related Services.
Then this is dumped into a string:
sql_field_string = main_service
I thought that removing the LIMIT would index all related services, but it’s still only indexing the first. How should I append this query to include all the others as well? Or at lease what should I be looking for?
Thank you in advance.
