-1

I'm currently having some trouble with a search query I'm doing in MySQL.

I have a table that stores form data for different fields of a course, with two foreign keys (courseid and fieldid). There is a data field that stores the form data of the field for the course. The table is structured as such:

id | courseid | fieldid | data

I'm doing an advanced search for courses where a user can specify the fields they want to filter. A few of these fields query this one table. Note: I cannot use courseid as I'm joining this table with another table. The other table is the course table itself and I search for the name of the course there.

I have a query like this:

SELECT DISTINCT c.name FROM `course_table` c
INNER JOIN `form_table` f ON c.id = f.courseid
WHERE (c.name LIKE '%hah%')
AND (f.fieldid = 2 AND f.data BETWEEN 1371160800 AND 1529000797) 
AND (f.fieldid = 8 AND f.data LIKE '%foo%') 
AND (f.fieldid = 4 AND f.data LIKE 'bar%') 
AND (f.fieldid = 1 AND f.data LIKE '%gah%')
ORDER BY c.name

The first clause is a date comparison, and the rest are simply string searches.

This will return nothing as there are too many ANDs. I tried with ORs, but I essentially get every course instead of the one I want (the combined data is supposed to be specific to a single course).

I have looked through other solutions, some suggesting to use the IN clause (i.e. fieldid in (2, 8, 4, 1)) combined with GROUP BY (on the courseid) and HAVING, but that also did not help in my case.

2

It is the common EAV.

I have looked through other solutions, some suggesting to use the IN clause (i.e. fieldid in (2, 8, 4, 1)) combined with GROUP BY (on the courseid) and HAVING, but that also did not help in my case.

It is correct method. But you misused it. You must check not fieldid, but (fieldid, f.data) pair:

SELECT c.name 
FROM `course_table` c
INNER JOIN `form_table` f ON c.id = f.courseid
WHERE (c.name LIKE '%hah%')
AND (    (f.fieldid = 2 AND f.data BETWEEN 1371160800 AND 1529000797) 
      OR (f.fieldid = 8 AND f.data LIKE '%foo%') 
      OR (f.fieldid = 4 AND f.data LIKE 'bar%') 
      OR (f.fieldid = 1 AND f.data LIKE '%gah%')
    )
GROUP BY c.name
HAVING COUNT( /* DISTINCT */ f.fieldid) = 4
  • It did not work completely (i.e. some courses do not have 'bar' in their data for field 4 but still show up), but it did narrow my results a lot more. I will work with this and try to get it working for my needs. – Merikato Jun 15 '18 at 12:49
  • @Merikato If so use COUNT(DISTINCT f.field, f.data) – Akina Jun 15 '18 at 15:34
  • That worked perfectly! – Merikato Jun 15 '18 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.