CODEWITHSUNDEEP
mysqlregexjoin
serg66
serg66

Reputation: 1148

REGEXP in MYSQL Join works improperly

I have a next mysql query:

SELECT 
    shop_sections.title, 
    shop_sections.id, 
    shop_sections.parent_id, 
    count(shop_items.id) AS items
FROM 
    shop_sections 
LEFT JOIN 
    shop_items 
ON 
    shop_items.section_id 
        REGEXP "^" + shop_sections.parent_id + "([[.full-stop.]].*|$)" 
GROUP BY 
    shop_sections.id

REGEXP perform not properly. For row shop_items.section_id = 1 it gets all rows with field shop_items.section_id that are started with '1' (i.e. 13.14 or 13.15, but it must get whether 1 or 1.* (1.1, 1.2, 1.2.3 and the like)).

I've noticed that if change

REGEXP "^" + shop_sections.parent_id + "([[.full-stop.]].*|$)"

to

REGEXP "^1([[.full-stop.]].*|$)"

than it works properly for 1 (and also for any value if insert it to the query manually). But I want to fetch all items using one query.

What may be the problem?

UPD.

shop_items.section_id contains values in the next form: parent_section.child_section_1.child_section_2.child_section_3 etc. I need this for pagination on item's pages.

Values in shop_sections.parent_id have the same form.

All I want to do is to show a tree of sections and a number of items for every section.

Upvotes: 2

Views: 7058

Answers (1)

ttzn
ttzn

Reputation: 2623

+ can't be used for concatenation in MySQL. Your test should be :

ON 
  shop_items.section_id 
    REGEXP CONCAT('^', shop_sections.parent_id, '([[.full-stop.]].*|$)')

The strange results you get are due to the broken concatenation performing an addition instead. Suppose shop_sections.parent_id is 1, then :

'^' + shop_sections.parent_id + '([[.full-stop.]].*|$)'

... is really evaluated 0 + 1 + 0 (strings cast as integers are evaluated as 0 unless they begin with a sequence of numbers), which used as a regular expression is equivalent to a useless '1'.

Upvotes: 3

Related Questions