Jump to content

How to execute a subquery


ThibaultWUN
 Share

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 ?

Share this post


Link to post
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)
 

Share this post


Link to post
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%.

Share this post


Link to post
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...

Share this post


Link to post
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)

 

Share this post


Link to post
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)

Share this post


Link to post
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)

Share this post


Link to post
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)

Share this post


Link to post
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 !

Share this post


Link to post
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
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More