Jump to content

How to execute a subquery


ThibaultWUN

Recommended Posts

I have a subquery that works well in phpMyAdmin but I cannot make it work in prestashop.

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

What do I have to change for this code to work is Prestashop ? How can I make a subquery ?

Link to comment
Share on other sites

imho the query should look like


$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

(otherwise you can get error if

SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5

returns more the one row)
 

Link to comment
Share on other sites

On 10/13/2020 at 4:32 PM, Guest said:

Do you want to put this in your previous module?

It is in a different module.

On 10/13/2020 at 4:33 PM, Guest said:

Need to get a list as a spreadsheet?
Helper List?

You have to describe everything in more detail.

I have groups called admin_%companyName% and groups called user_%companyName%.

If a user of the group user_myCompany is logged in, he can only have access to his own data. If a user of the group admin_myCompany is logged in, he must have access to his own data and to the data of all the users of the group user_myCompany. If the current user is in a group admin_%companyName%, I make a substring to get the company name and I create a new string "user_" + $companyName. That is the string I am using in my subquery.

A user can only be in ONE group of the type admin_%companyName% or ONE group of the type user_%companyName%.

Link to comment
Share on other sites

On 10/13/2020 at 9:23 PM, EvaF said:

imho the query should look like



$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)');

(otherwise you can get error if


SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5

returns more the one row)
 

Ok thanks, I'm going to try like that although my subquery should not return more than one row...

Link to comment
Share on other sites

I don't argue - because i don't know your data - I only say that

the construction:
 

...WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

is very unusual and leads to mysql-error if result of subquery returns more than 1 row - that's all

That's a reason  I have suggested:

...WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

 

Link to comment
Share on other sites

44 minutes ago, EvaF said:

I don't argue - because i don't know your data - I only say that

the construction:
 


...WHERE id_group LIKE (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

is very unusual and leads to mysql-error if result of subquery returns more than 1 row - that's all

That's a reason  I have suggested:


...WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE '%MYGROUP%' AND id_lang=5)

 

And you are right, it looks cleaner that way. But it still does not work. What's weird is that I tried this query in phpMyAdmin and it works...

SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'mygroup' AND id_lang=5)

And I tried to make two queries instead of one but it does not work either.

$idGroup = Db::getInstance()->executeS('SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE 'mygroup' AND id_lang=5');

It also makes my website crash... And again, it works in phpMyAdmin !

Edited by ThibaultWUN (see edit history)
Link to comment
Share on other sites

I am not sure, if you write correct 'mygroup'

 in the case of group:  user_%companyName%  

thus in specific case you want to find f.e. user_AAACompany

don't  forget that in mysql  underscore character "_" is considered as wildcard  - i.e. you should write

LIKE 'user\_AAACompany'

and result query should look like:

SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'user\_AAACompany' AND id_lang=5)

 

Edited by EvaF (see edit history)
Link to comment
Share on other sites

49 minutes ago, ThibaultWUN said:

$idGroup = Db::getInstance()->executeS('SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE 'mygroup' AND id_lang=5');

as per my understanding, mygroup should be in double quotes, so can you please try with below query:

$list = Db::getInstance()->executeS('SELECT id_customer FROM `'._DB_PREFIX_.'customer_group` WHERE id_group IN (SELECT id_group FROM `'._DB_PREFIX_.'group_lang` WHERE name LIKE "%MYGROUP%" AND id_lang=5)');

Let's give a try with this one and see what will be result..

Edited by Rachit Agarwal (see edit history)
Link to comment
Share on other sites

On 10/15/2020 at 10:50 AM, EvaF said:

I am not sure, if you write correct 'mygroup'

 in the case of group:  user_%companyName%  

thus in specific case you want to find f.e. user_AAACompany

don't  forget that in mysql  underscore character "_" is considered as wildcard  - i.e. you should write


LIKE 'user\_AAACompany'

and result query should look like:


SELECT id_customer FROM ps_customer_group WHERE id_group IN (SELECT id_group FROM ps_group_lang WHERE name LIKE 'user\_AAACompany' AND id_lang=5)

 

Yes ! I forgot about the wildcard ! It is much better now !

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...