CODEWITHSUNDEEP
pythonmysqlsqlpython-2.7
stingMantis
stingMantis

Reputation: 340

Use a single query to pull from mulitple tables

I have a couple of queries that I feel could be refactored and done easier in mySQL but I'm not sure how to go about it. I'm doing it programtically but I'm sure I can speed this up.

Basically get an id from the user, look in the db and get the ids of rows that may have similiar tags as the given argument. making sure to exclude the original argument and not including any duplicate ids.

Is there a way to do this in pure sql?

here's my current code:

def getRelatedEvents(self, memberId, eventId):
    relatated_events = []

    # first we get all the tags related to this event
    for tag in self.db.query("select tagName from event_tags where eventId={}".format(eventId)):
        # we iterate through each tag and find the eventIds for it
        events = self.db.query("SELECT eventId from event_tags where tagName LIKE %s and eventId != %s LIMIT 3",
                               '%'+tag['tagName']+'%', eventId)

    # we group them in a list, excluding ones that are already in here
        for id in events:
            if id['eventId'] not in relatated_events:
                relatated_events.append(id['eventId'])

    # we get the extra event info for each item in the related list and return
    return [self.getSpecificEvent(memberId, item) for item in relatated_events]

Upvotes: 1

Views: 71

Answers (1)

GMB
GMB

Reputation: 222582

You should be able to achieve this with a self-join, like :

SELECT DISTINCT e2.eventId
FROM event_tags e1
INNER JOIN event_tags e2 
    ON e2.tagName LIKE CONCAT('%', e1.tagName, '%') AND e2.eventId != e1.eventId
WHERE e1.eventId = {}

I notice that the second query has a LIMIT 3 clause. First of all, please note that without an ORDER BY clause this does not produces predictable results. Here is a solution based on window function ROW_NUMBER() (available in MySQL 8), that will produce not more than 3 event_id for each matching tag :

SELECT DISTINCT event_id FROM (
    SELECT e2.eventId, ROW_NUMBER() OVER(PARTITION BY e1.eventId ORDER BY e2.eventId) rn
    FROM event_tags e1
    INNER JOIN event_tags e2 
        ON e2.tagName LIKE CONCAT('%', e1.tagName, '%') AND e2.eventId != e1.eventId
    WHERE e1.eventId = {}
) WHERE rn <= 3

Upvotes: 2

Related Questions