L
L
lexstile2021-02-01 21:53:13
SQL
lexstile, 2021-02-01 21:53:13

How to refine a query for searching (LIKE) on several tables?

There is a request:

SELECT DISTINCT events.id, events.name as event_name, events.date_start, events.date_end, events.country_id, events.description, events.day_1, events.day_2, events.month_1, events.month_2, events.year_1, events.year_2, events.is_present, links.links, images.images, countries.countries, countries.country_ids, country_to_cluster.year_start FROM events
    LEFT JOIN (SELECT countries.id, GROUP_CONCAT(countries.id) as country_ids,  event_to_country.country_id as country_id, event_to_country.event_id, GROUP_CONCAT(countries.name) as countries FROM countries LEFT JOIN event_to_country ON countries.id = country_id GROUP BY event_to_country.event_id) as countries ON events.id = countries.event_id
    LEFT JOIN (SELECT event_id, GROUP_CONCAT(link) as links FROM links GROUP BY event_id) AS links ON events.id = links.event_id
    LEFT JOIN (SELECT event_id, GROUP_CONCAT(name) as images FROM images GROUP BY event_id) AS images ON events.id = images.event_id
    LEFT JOIN keywords ON events.id = keywords.event_id
    LEFT JOIN country_to_cluster ON (country_to_cluster.year_start IS NULL AND country_to_cluster.year_end IS NULL && country_to_cluster.country_id = events.country_id) OR (country_to_cluster.country_id = events.country_id AND events.year_1 >= country_to_cluster.year_start AND events.year_1 <= country_to_cluster.year_end)
    WHERE (events.name LIKE :search || events.description LIKE :search || keywords.name LIKE :search || countries.countries LIKE :search)  GROUP BY events.id

There are several tables:
+ events - events
+ countries - countries
+ clusters - clusters
+ event_to_country - links of events and countries (one event can have several countries)
+ country_to_cluster - links of countries and clusters (one country can have several clusters)

countries
60184c5103ede974764229.png

clusters
60184c7c1e8be266568106.png

event_to_country
60184cb47abbd522734106.png

country_to_cluster
60184d00567d5614938058.png

How to add search by cluster name (clusters.name) to the current query?
That is, the user enters the name of the cluster, and I display events that are associated with this cluster through intermediate tables - event_to_country and country_to_cluster .

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question