Complex search query with multiple AND/OR conditions

Databases
1

Hello,
I’m working on a searching system which requires lot of AND/OR conditions. Basically I want to search for posts into the database with Treatment and Location as search term. It’ll search for Clinic Locations in ‘_dac_clinic_location’ and Professionals Location in ‘_dac_location’ , also search for treatments in ‘_dac_profession’ field and ‘_dac_treatments_procedures’ field. I have the query below, but it’s not fetching anything. Can you please help on how to go about this query? Appreciate any help on this.

Thanks

    $sql = "SELECT $meta_table.post_id, $post_table.post_status 
            From $meta_table, $post_table 
            WHERE $post_table.post_status <> 'hold' 
            AND $meta_table.post_id=$post_table.ID 
            AND(
                (($meta_table.meta_key='_dac_profession' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_location' AND $meta_table.meta_value LIKE '%$location%')) 
                OR 
                    (($meta_table.meta_key='_dac_treatments_procedures' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_location' AND $meta_table.meta_value LIKE '%$location%')) 
                OR 
                    (($meta_table.meta_key='_dac_profession' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_additional_location' AND $meta_table.meta_value LIKE '%$location%')) 
                OR 
                    (($meta_table.meta_key='_dac_treatments_procedures' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_additional_location' AND $meta_table.meta_value LIKE '%$location%')) 
                OR 
                    (($meta_table.meta_key='_dac_profession' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_clinic_location' AND $meta_table.meta_value LIKE '%$location%')) 
                OR 
                    (($meta_table.meta_key='_dac_treatments_procedures' AND $meta_table.meta_value LIKE '%$treatment%') 
                    AND ($meta_table.meta_key='_dac_clinic_location' AND $meta_table.meta_value LIKE '%$location%'))
            ) 
            LIMIT $offset, $rowsPerPage";
1 Like
2

You’re looking for two different values in the same fields, and that’ll never occur. If you’ll have two separate records tied to the same record, then you’ll need to do an additional join

    ($meta_table.meta_key='_dac_profession' AND $meta_table.meta_value LIKE '%$treatment%') 
AND ($meta_table.meta_key='_dac_location' AND $meta_table.meta_value LIKE '%$location%'))
1 Like
3

Hi @DaveMaxwell,
Thanks for your help. Can you please give an example how to achieve this?

4

SELECT $meta_table.post_id , $post_table.post_status FROM $post_table INNER JOIN $meta_table AS m1 ON m1.post_id = $post_table.ID AND m1.meta_key IN ( '_dac_profession' , '_dac_treatments_procedures') AND m1.meta_value LIKE '%$treatment%') INNER JOIN $meta_table AS m2 ON m2.post_id = $post_table.ID AND m2.meta_key IN ( '_dac_location' , '_dac_additional_location' , '_dac_clinic_location') AND m2.meta_value LIKE '%$location%') ORDER BY something_so_LIMIT_makes_sense LIMIT $offset , $rowsPerPage

1 Like
5

Thanks @r937, I’ve used your sql. It’s still not returning anything.

6

Thank you @r937, it’s working after I modified this little bit. It has an extra parenthesis in the query LIKE ‘%$treatment%’) and LIKE ‘%$location%’) :smile:

7

good job… sorry about the error, i missed those!

8

Thanks @r937 for your help on this.
I’m having another issue with this SQL. I want to order the result by the ‘dac_distance’ meta value. I’ve this sql for it now - But it’s not ordering the result as expected. Is there anything missing?

“SELECT DISTINCT m1.post_id, $post_table.post_status
FROM $post_table
INNER JOIN $meta_table AS m1
ON m1.post_id = $post_table.ID
AND m1.meta_key IN (‘_dac_profession’, ‘_dac_treatments_procedures’)
AND m1.meta_value LIKE ‘%$treatment%’
INNER JOIN $meta_table AS m2
ON m2.post_id = $post_table.ID
AND m2.meta_key IN (‘_dac_location’, ‘_dac_additional_location’, ‘_dac_clinic_location’)
AND m2.meta_value LIKE ‘%$location%’
INNER JOIN $meta_table AS m3
ON m3.post_id = $post_table.ID
AND m3.meta_key IN (‘_dac_distance’)
AND m3.meta_value <> ‘’
WHERE $post_table.post_status <> ‘hold’
ORDER BY m3.meta_value ASC
LIMIT $offset, $rowsPerPage”

9

i think it’s your use of DISTINCT that makes the results unexpected

it’s ~always~ a good idea to include the column that you want to sort on in the SELECT list

10

@r937 I’ve removed DISTINCT from the sql and added m3.meta_value in SELECT statement, but still the results not sorted in ASC order.

11

I have a feeling they very well may be, just not in the way you want them.

Is it a case thing?
http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

Or maybe collation?
http://dev.mysql.com/doc/refman/5.7/en/charset-collation-effect.html

 The following table shows the resulting order of the values 
 if we use ORDER BY with different collations.
 
latin1_swedish_ci   latin1_german1_ci   latin1_german2_ci
Muffler             Muffler             Müller
MX Systems          Müller              Muffler
Müller              MX Systems          MX Systems
12

my bet is on VARCHAR storing left-aligned numbers, e.g.

12.2
14.4
155555.55
2.2
200,20
9.37

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.