Flexi-Search workaround Too many tables; MySQL can only use 61 tables in a join

[Former Member]

Hi Experts,

we have 62 languages in our system. And we want to get all items where a localized attribute has a specific value:

 select count({pk}) from {MyProduct},{CatalogVersion as CV}, {Catalog as C} where {catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND ({C:id}='XX' and {CV:version} != 'Global') and ({activeLoc[ar_ae]:o}= true OR 
 {activeLoc[cs]:o}= true OR 
 {activeLoc[de]:o}= true OR 
 {activeLoc[de_at]:o}= true OR 
 {activeLoc[de_ch]:o}= true OR 
 {activeLoc[dk]:o}= true OR 
 {activeLoc[el]:o}= true OR 
 {activeLoc[en]:o}= true OR 
 {activeLoc[en-AU]:o}= true OR 
 {activeLoc[en-IN]:o}= true OR 
 {activeLoc[en-uk]:o}= true OR 
 {activeLoc[en_ae]:o}= true OR 
 {activeLoc[en_ca]:o}= true OR 
 {activeLoc[en_ch]:o}= true OR 
 {activeLoc[en_eu]:o}= true OR 
 {activeLoc[en_id]:o}= true OR 
 {activeLoc[en_imea]:o}= true OR 
 {activeLoc[en_la]:o}= true OR 
 {activeLoc[en_my]:o}= true OR 
 {activeLoc[en_ph]:o}= true OR 
 {activeLoc[en_sa]:o}= true OR 
 {activeLoc[en_sg]:o}= true OR 
 {activeLoc[en_th]:o}= true OR 
 {activeLoc[en_vn]:o}= true OR 
 {activeLoc[en_za]:o}= true OR 
 {activeLoc[es]:o}= true OR 
 {activeLoc[es_la]:o}= true OR 
 {activeLoc[et]:o}= true OR 
 {activeLoc[fi]:o}= true OR 
 {activeLoc[fr]:o}= true OR 
 {activeLoc[fr_be]:o}= true OR 
 {activeLoc[fr_ca]:o}= true OR 
 {activeLoc[fr_ch]:o}= true OR 
 {activeLoc[he]:o}= true OR 
 {activeLoc[hr]:o}= true OR 
 {activeLoc[hu]:o}= true OR 
 {activeLoc[id]:o}= true OR 
 {activeLoc[it]:o}= true OR 
 {activeLoc[it_ch]:o}= true OR 
 {activeLoc[ja]:o}= true OR 
 {activeLoc[kr]:o}= true OR 
 {activeLoc[lt]:o}= true OR 
 {activeLoc[lv]:o}= true OR 
 {activeLoc[nl]:o}= true OR 
 {activeLoc[nl_be]:o}= true OR 
 {activeLoc[no]:o}= true OR 
 {activeLoc[pl]:o}= true OR 
 {activeLoc[pt]:o}= true OR 
 {activeLoc[pt_la]:o}= true OR 
 {activeLoc[ro]:o}= true OR 
 {activeLoc[ru]:o}= true OR 
 {activeLoc[ru_ua]:o}= true OR 
 {activeLoc[se]:o}= true OR 
 {activeLoc[sk]:o}= true OR 
 {activeLoc[sl]:o}= true OR 
 {activeLoc[th]:o}= true OR 
 {activeLoc[tr]:o}= true OR 
 {activeLoc[uk]:o}= true OR 
 {activeLoc[vi]:o}= true OR 
 {activeLoc[zh]:o}= true OR 
 {activeLoc[zh_tw]:o}= true )

If I have exactly 58 checks, it works fine, if I have 59 I'm getting the Exception from mysql in the title. Any ideas to workaround this using flexi search? Thanks!

[Former Member · Accepted solution]

It might not be widely known but if you're lust looking for any localized value of 'activeLoc' to match you can do this:

 select count({p.pk}) 
 from {MyProduct as p
 JOIN CatalogVersion as CV on {p.catalogversion}={CV:PK}
 JOIN Catalog as C on {CV:catalog}={C:PK} } 
 where 
 {C:id}='XX' and {CV:version} <> 'Global' and 
 {p.activeLoc[ANY]}=true

The [ANY] language option internally joins the LP table without specifying the language. However this also means that you may have multiple occurrences of the same PK in the result when more than LP record matches. Here you could try SELECT DISTINCT.

[Former Member · Accepted solution]

Axel - amazing, this is exactly what I was looking for. This [any] syntax made my day/week 😉 Thanks!!!

[cieslo]

Hi,

this 61 limit in joins counts towards queries and doesn’t count when using subqueries. You could use select from subquery which consists of 61 joins and join to another subquery wich consists of 61 joins and so on. You could use a UNION statement too. In this scenario you could use something like this:

 select count(un.pk)
 from (
   {{
 select {pk} from {MyProduct},{CatalogVersion as CV}, {Catalog as C} where {catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND ({C:id}=‘XX' and {CV:version} != ‘Global') and {activeLoc[en]:o} = true
   }}
 union
   {{
 select {pk} from {Product},{CatalogVersion as CV}, {Catalog as C} where {catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND ({C:id}=‘XX' and {CV:version} != ‘Global') and {activeLoc[de]:o} = true
   }}
 union
   {{
 select {pk} from {Product},{CatalogVersion as CV}, {Catalog as C} where {catalogversion}={CV:PK} AND {CV:catalog}={C:PK} AND ({C:id}=‘XX' and {CV:version} != ‘Global') and {activeLoc[zh]:o} = true
   }}
 union
 ...
 ) as un

or a simpler version:

 select count({MP.pk})
 from
 {MyProduct as MP JOIN CatalogVersion as CV ON {catalogversion}={CV:PK} JOIN Catalog as C ON {CV:catalog}={C:PK}}
 where
 ({C:id}='XX' and {CV:version} != 'Global’)
 and exists ( {{select LP.ITEMPK FROM PRODUCTSLP as LP WHERE LP.ITEMPK = {MP:PK} and lp.p_activeLoc = true}} )

[Former Member]

The simple version is amazing, thanks so much!

[Former Member]

I think you need to rework your query a bit. I'm not really sure the goal, but your query is getting a count, and from what I can tell, will count the product if at least one language has activeLoc =true.

But if you really want to do a queries based on localized attributes, you may want to access the localized table directly, for products it's productslp. This table has all the languages so you wouldn't need a join since it's all in the same table.

[Former Member]

Thanks for the answer Matt. The count is just a test, in the end we need the PK. I got your Idea to directly use productslp table, and I think it's a really good Idea and I will check this if there is no way to do it directly in flexi search. When you say rework a bit, do you have some more details?

[Former Member]

I would probably keep it as is, until the activeLoc part, and then do a sub query like ({{ exists select {pk} from productslp where itempk = {p:pk} and activeLoc = true }})

You should be able to access the table directly, without the { } flexible search will just pass it directly to the database.