How to get prestashop product attributes through SQL
I need to build a report for a prestashop site showing product id, name, and stock. So far I've done just that through the following query:
SELECT product.id_product, product_lang.name, stock_available.quantity
FROM ps_product product, ps_product_lang product_lang, ps_stock_available stock_available
WHERE stock_available.id_product = product.id_product
AND stock_available.id_product = product.id_product
AND product_lang.id_lang =1
AND product.reference = ''
AND product_lang.id_product = product.id_product
which outputs something like:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
|===============================|
The problem is that some products have the same name and same ID but different attributes, so i'm getting stuff like this:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
| 1 | earring | 76 |
| 1 | earring | 9 |
|===============================|
What i need is to add a new field showing the attributes that differentiate them, so the final output would be something like this:
|================================================|
| id | name | attributes | stock |
| 1 | earring | yellow, short | 45 |
| 1 | earring | red, short | 76 |
| 1 | earring | red, long | 9 |
|================================================|
But I can't figure out how the attributes tables (because there are several of them) relate to the products table in order to add the proper statements to the query and get the desired behaviour.
If there are any prestashop database experts around, or anyone that could help, I would really appreciate it.
mysql sql database relational-database prestashop
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
add a comment |
I need to build a report for a prestashop site showing product id, name, and stock. So far I've done just that through the following query:
SELECT product.id_product, product_lang.name, stock_available.quantity
FROM ps_product product, ps_product_lang product_lang, ps_stock_available stock_available
WHERE stock_available.id_product = product.id_product
AND stock_available.id_product = product.id_product
AND product_lang.id_lang =1
AND product.reference = ''
AND product_lang.id_product = product.id_product
which outputs something like:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
|===============================|
The problem is that some products have the same name and same ID but different attributes, so i'm getting stuff like this:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
| 1 | earring | 76 |
| 1 | earring | 9 |
|===============================|
What i need is to add a new field showing the attributes that differentiate them, so the final output would be something like this:
|================================================|
| id | name | attributes | stock |
| 1 | earring | yellow, short | 45 |
| 1 | earring | red, short | 76 |
| 1 | earring | red, long | 9 |
|================================================|
But I can't figure out how the attributes tables (because there are several of them) relate to the products table in order to add the proper statements to the query and get the desired behaviour.
If there are any prestashop database experts around, or anyone that could help, I would really appreciate it.
mysql sql database relational-database prestashop
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
add a comment |
I need to build a report for a prestashop site showing product id, name, and stock. So far I've done just that through the following query:
SELECT product.id_product, product_lang.name, stock_available.quantity
FROM ps_product product, ps_product_lang product_lang, ps_stock_available stock_available
WHERE stock_available.id_product = product.id_product
AND stock_available.id_product = product.id_product
AND product_lang.id_lang =1
AND product.reference = ''
AND product_lang.id_product = product.id_product
which outputs something like:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
|===============================|
The problem is that some products have the same name and same ID but different attributes, so i'm getting stuff like this:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
| 1 | earring | 76 |
| 1 | earring | 9 |
|===============================|
What i need is to add a new field showing the attributes that differentiate them, so the final output would be something like this:
|================================================|
| id | name | attributes | stock |
| 1 | earring | yellow, short | 45 |
| 1 | earring | red, short | 76 |
| 1 | earring | red, long | 9 |
|================================================|
But I can't figure out how the attributes tables (because there are several of them) relate to the products table in order to add the proper statements to the query and get the desired behaviour.
If there are any prestashop database experts around, or anyone that could help, I would really appreciate it.
mysql sql database relational-database prestashop
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
I need to build a report for a prestashop site showing product id, name, and stock. So far I've done just that through the following query:
SELECT product.id_product, product_lang.name, stock_available.quantity
FROM ps_product product, ps_product_lang product_lang, ps_stock_available stock_available
WHERE stock_available.id_product = product.id_product
AND stock_available.id_product = product.id_product
AND product_lang.id_lang =1
AND product.reference = ''
AND product_lang.id_product = product.id_product
which outputs something like:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
|===============================|
The problem is that some products have the same name and same ID but different attributes, so i'm getting stuff like this:
|===============================|
| id | name | stock |
| 1 | earring | 45 |
| 1 | earring | 76 |
| 1 | earring | 9 |
|===============================|
What i need is to add a new field showing the attributes that differentiate them, so the final output would be something like this:
|================================================|
| id | name | attributes | stock |
| 1 | earring | yellow, short | 45 |
| 1 | earring | red, short | 76 |
| 1 | earring | red, long | 9 |
|================================================|
But I can't figure out how the attributes tables (because there are several of them) relate to the products table in order to add the proper statements to the query and get the desired behaviour.
If there are any prestashop database experts around, or anyone that could help, I would really appreciate it.
mysql sql database relational-database prestashop
mysql sql database relational-database prestashop
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
asked Jun 28 '18 at 14:33
F_BassF_Bass
327
327
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try this:
SELECT DISTINCT
p.id_product, pl.name, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_stock_available psa ON (p.id_product = psa.id_product)
GROUP by p.id_product
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51085548%2fhow-to-get-prestashop-product-attributes-through-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try this:
SELECT DISTINCT
p.id_product, pl.name, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_stock_available psa ON (p.id_product = psa.id_product)
GROUP by p.id_product
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
add a comment |
Try this:
SELECT DISTINCT
p.id_product, pl.name, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_stock_available psa ON (p.id_product = psa.id_product)
GROUP by p.id_product
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
add a comment |
Try this:
SELECT DISTINCT
p.id_product, pl.name, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_stock_available psa ON (p.id_product = psa.id_product)
GROUP by p.id_product
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
Try this:
SELECT DISTINCT
p.id_product, pl.name, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_stock_available psa ON (p.id_product = psa.id_product)
GROUP by p.id_product
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
answered Nov 14 '18 at 14:18
icl 2684icl 2684
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51085548%2fhow-to-get-prestashop-product-attributes-through-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
