How to get the value from the other table if one table column is empty
I have two tables in my database
- product
- product_variations
Both have price columns.
If any product has variation then the price column of the product table will be nullified and the price column in the variation table will have some value.
But if product has no variation then the price column of product table will have some value but the price column in the variation table will not have a value
I want to fetch all the products having low to high price and vice versa.
$shopProducts = product::where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderBy('price', 'asc')
->paginate(12);
The above query is returning all the products in correct and place all those products at the end those have variation and have price column null.
But I want a query which checks first the price column of product and if it is null, then go to product variation table and search for a value and then return the result in either desc or asc.
php mysql laravel eloquent
edited yesterday
treyBake
3,0013832
asked yesterday
adnan shaukat
11
add a comment |
I have two tables in my database
- product
- product_variations
Both have price columns.
If any product has variation then the price column of the product table will be nullified and the price column in the variation table will have some value.
But if product has no variation then the price column of product table will have some value but the price column in the variation table will not have a value
I want to fetch all the products having low to high price and vice versa.
$shopProducts = product::where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderBy('price', 'asc')
->paginate(12);
The above query is returning all the products in correct and place all those products at the end those have variation and have price column null.
But I want a query which checks first the price column of product and if it is null, then go to product variation table and search for a value and then return the result in either desc or asc.
php mysql laravel eloquent
edited yesterday
treyBake
3,0013832
asked yesterday
adnan shaukat
11
1
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
yes i need a such solution
– adnan shaukat
yesterday
add a comment |
I have two tables in my database
- product
- product_variations
Both have price columns.
If any product has variation then the price column of the product table will be nullified and the price column in the variation table will have some value.
But if product has no variation then the price column of product table will have some value but the price column in the variation table will not have a value
I want to fetch all the products having low to high price and vice versa.
$shopProducts = product::where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderBy('price', 'asc')
->paginate(12);
The above query is returning all the products in correct and place all those products at the end those have variation and have price column null.
But I want a query which checks first the price column of product and if it is null, then go to product variation table and search for a value and then return the result in either desc or asc.
php mysql laravel eloquent
edited yesterday
treyBake
3,0013832
asked yesterday
adnan shaukat
11
I have two tables in my database
- product
- product_variations
Both have price columns.
If any product has variation then the price column of the product table will be nullified and the price column in the variation table will have some value.
But if product has no variation then the price column of product table will have some value but the price column in the variation table will not have a value
I want to fetch all the products having low to high price and vice versa.
$shopProducts = product::where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderBy('price', 'asc')
->paginate(12);
The above query is returning all the products in correct and place all those products at the end those have variation and have price column null.
But I want a query which checks first the price column of product and if it is null, then go to product variation table and search for a value and then return the result in either desc or asc.
php mysql laravel eloquent
php mysql laravel eloquent
edited yesterday
treyBake
3,0013832
asked yesterday
adnan shaukat
11
edited yesterday
treyBake
3,0013832
asked yesterday
adnan shaukat
11
edited yesterday
treyBake
3,0013832
edited yesterday
treyBake
3,0013832
edited yesterday
treyBake
3,0013832
3,0013832
asked yesterday
adnan shaukat
11
asked yesterday
adnan shaukat
11
asked yesterday
adnan shaukat
11
11
1
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
yes i need a such solution
– adnan shaukat
yesterday
add a comment |
1
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
yes i need a such solution
– adnan shaukat
yesterday
1
1
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
yes i need a such solution
– adnan shaukat
yesterday
yes i need a such solution
– adnan shaukat
yesterday
add a comment |
1 Answer
1
active
oldest
votes
I don't know of a completely native way to do this with query builder, but if you're using a sql database you should be able to do this with a raw case statement. For example ...
$shopProducts = product::SELECT(
DB::raw("CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END AS price")
)->where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderByRaw('CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END', 'asc')
->paginate(12);
That should work with mssql or mysql and most databases support some type of similar syntax. You'll just need to add any additional columns you want to your select statement. You will also need to join your product_variations table for this method to work.
The raw select and raw order by both bypass Laravel's query builder and pass your statements directly to the database, so just be careful how you construct your queries. Laravel won't sanitize those for you.
answered 22 hours ago
AndyChern
1713
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%2f53943647%2fhow-to-get-the-value-from-the-other-table-if-one-table-column-is-empty%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
I don't know of a completely native way to do this with query builder, but if you're using a sql database you should be able to do this with a raw case statement. For example ...
$shopProducts = product::SELECT(
DB::raw("CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END AS price")
)->where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderByRaw('CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END', 'asc')
->paginate(12);
That should work with mssql or mysql and most databases support some type of similar syntax. You'll just need to add any additional columns you want to your select statement. You will also need to join your product_variations table for this method to work.
The raw select and raw order by both bypass Laravel's query builder and pass your statements directly to the database, so just be careful how you construct your queries. Laravel won't sanitize those for you.
answered 22 hours ago
AndyChern
1713
add a comment |
I don't know of a completely native way to do this with query builder, but if you're using a sql database you should be able to do this with a raw case statement. For example ...
$shopProducts = product::SELECT(
DB::raw("CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END AS price")
)->where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderByRaw('CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END', 'asc')
->paginate(12);
That should work with mssql or mysql and most databases support some type of similar syntax. You'll just need to add any additional columns you want to your select statement. You will also need to join your product_variations table for this method to work.
The raw select and raw order by both bypass Laravel's query builder and pass your statements directly to the database, so just be careful how you construct your queries. Laravel won't sanitize those for you.
answered 22 hours ago
AndyChern
1713
add a comment |
I don't know of a completely native way to do this with query builder, but if you're using a sql database you should be able to do this with a raw case statement. For example ...
$shopProducts = product::SELECT(
DB::raw("CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END AS price")
)->where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderByRaw('CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END', 'asc')
->paginate(12);
That should work with mssql or mysql and most databases support some type of similar syntax. You'll just need to add any additional columns you want to your select statement. You will also need to join your product_variations table for this method to work.
The raw select and raw order by both bypass Laravel's query builder and pass your statements directly to the database, so just be careful how you construct your queries. Laravel won't sanitize those for you.
answered 22 hours ago
AndyChern
1713
I don't know of a completely native way to do this with query builder, but if you're using a sql database you should be able to do this with a raw case statement. For example ...
$shopProducts = product::SELECT(
DB::raw("CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END AS price")
)->where(
[
"publish" => 'PUBLISH',
'shop_id' => input::get('shopId')
]
)->orderByRaw('CASE WHEN product_variations.price IS NULL THEN product.price ELSE product_variations.price END', 'asc')
->paginate(12);
That should work with mssql or mysql and most databases support some type of similar syntax. You'll just need to add any additional columns you want to your select statement. You will also need to join your product_variations table for this method to work.
The raw select and raw order by both bypass Laravel's query builder and pass your statements directly to the database, so just be careful how you construct your queries. Laravel won't sanitize those for you.
answered 22 hours ago
AndyChern
1713
answered 22 hours ago
AndyChern
1713
answered 22 hours ago
AndyChern
1713
answered 22 hours ago
AndyChern
1713
1713
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53943647%2fhow-to-get-the-value-from-the-other-table-if-one-table-column-is-empty%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
1
Do you need, for eg. if a product has null price so get all variation of this product and join to the result?
– Manuel Eduardo Romero
yesterday
yes i need a such solution
– adnan shaukat
yesterday