SQL Force Left Join to Print No Match
I have a problem with a query. I have a bunch of cars, and there are types of cars. For each type, a user can select one car, and I'm saving this in the database. When I want to retrieve the data I run into trouble:
[
{
"id_typecar": 1,
"nom_typecar": "Type17",
"id_car": 14,
"id_user": 3,
"cars": [
{
"id_car": 1,
"nom_car": "775",
}
},
{
"id_car": 2,
"nom_car": "048",
}
]
},
{
//Not displayed if user_id != 1 or null -> I want to display that
"id_typecar": 2,
"nom_typecar": "Type1",
"id_car": 13,
"id_user": 1, //expected output "id_user": null
"cars": [
{
"id_car": 11,
"nom_car": "123",
},
{
"id_car": 12,
"nom_car": "456,
},
{
"id_car": 17,
"nom_car": "789",
}
]
And my query :
return TypeCars::with('cars')
->leftJoin('cars_user', 'cars_user.id', '=', 'type_cars.id')
->where('cars_user.id' = 1)
->orWhereNull('cars_user.id')
->get()
->toJson();
I wanted to retrieve the data even if the id_user is not there yet because I must display types and cars. I added the leftJoin constraint with a where "null" or where cars_user.userid = ? but if user 3 chose one type of car that user 1 has not chosen (because it won't null or userid = 1), it would not display cars anymore.
Thank you!
EDIT :
Actual output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 2 Type2Car 13 |
| 2 Type2Car 13 |
| 3 Type3Car NULL |
+---------------------------------+
Expected output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 1 Type1Car NULL | <-- needed for the display
| 2 Type2Car 13 |
| 2 Type2Car 14 |
| 3 Type3Car NULL |
| 3 Type3Car NULL |
+---------------------------------+
If I don't have this line if the user 13 didn't specify any cars for this type it won't display since user 14 has chosen one
mysql sql laravel eloquent
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
|
show 4 more comments
I have a problem with a query. I have a bunch of cars, and there are types of cars. For each type, a user can select one car, and I'm saving this in the database. When I want to retrieve the data I run into trouble:
[
{
"id_typecar": 1,
"nom_typecar": "Type17",
"id_car": 14,
"id_user": 3,
"cars": [
{
"id_car": 1,
"nom_car": "775",
}
},
{
"id_car": 2,
"nom_car": "048",
}
]
},
{
//Not displayed if user_id != 1 or null -> I want to display that
"id_typecar": 2,
"nom_typecar": "Type1",
"id_car": 13,
"id_user": 1, //expected output "id_user": null
"cars": [
{
"id_car": 11,
"nom_car": "123",
},
{
"id_car": 12,
"nom_car": "456,
},
{
"id_car": 17,
"nom_car": "789",
}
]
And my query :
return TypeCars::with('cars')
->leftJoin('cars_user', 'cars_user.id', '=', 'type_cars.id')
->where('cars_user.id' = 1)
->orWhereNull('cars_user.id')
->get()
->toJson();
I wanted to retrieve the data even if the id_user is not there yet because I must display types and cars. I added the leftJoin constraint with a where "null" or where cars_user.userid = ? but if user 3 chose one type of car that user 1 has not chosen (because it won't null or userid = 1), it would not display cars anymore.
Thank you!
EDIT :
Actual output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 2 Type2Car 13 |
| 2 Type2Car 13 |
| 3 Type3Car NULL |
+---------------------------------+
Expected output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 1 Type1Car NULL | <-- needed for the display
| 2 Type2Car 13 |
| 2 Type2Car 14 |
| 3 Type3Car NULL |
| 3 Type3Car NULL |
+---------------------------------+
If I don't have this line if the user 13 didn't specify any cars for this type it won't display since user 14 has chosen one
mysql sql laravel eloquent
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16
|
show 4 more comments
I have a problem with a query. I have a bunch of cars, and there are types of cars. For each type, a user can select one car, and I'm saving this in the database. When I want to retrieve the data I run into trouble:
[
{
"id_typecar": 1,
"nom_typecar": "Type17",
"id_car": 14,
"id_user": 3,
"cars": [
{
"id_car": 1,
"nom_car": "775",
}
},
{
"id_car": 2,
"nom_car": "048",
}
]
},
{
//Not displayed if user_id != 1 or null -> I want to display that
"id_typecar": 2,
"nom_typecar": "Type1",
"id_car": 13,
"id_user": 1, //expected output "id_user": null
"cars": [
{
"id_car": 11,
"nom_car": "123",
},
{
"id_car": 12,
"nom_car": "456,
},
{
"id_car": 17,
"nom_car": "789",
}
]
And my query :
return TypeCars::with('cars')
->leftJoin('cars_user', 'cars_user.id', '=', 'type_cars.id')
->where('cars_user.id' = 1)
->orWhereNull('cars_user.id')
->get()
->toJson();
I wanted to retrieve the data even if the id_user is not there yet because I must display types and cars. I added the leftJoin constraint with a where "null" or where cars_user.userid = ? but if user 3 chose one type of car that user 1 has not chosen (because it won't null or userid = 1), it would not display cars anymore.
Thank you!
EDIT :
Actual output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 2 Type2Car 13 |
| 2 Type2Car 13 |
| 3 Type3Car NULL |
+---------------------------------+
Expected output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 1 Type1Car NULL | <-- needed for the display
| 2 Type2Car 13 |
| 2 Type2Car 14 |
| 3 Type3Car NULL |
| 3 Type3Car NULL |
+---------------------------------+
If I don't have this line if the user 13 didn't specify any cars for this type it won't display since user 14 has chosen one
mysql sql laravel eloquent
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
I have a problem with a query. I have a bunch of cars, and there are types of cars. For each type, a user can select one car, and I'm saving this in the database. When I want to retrieve the data I run into trouble:
[
{
"id_typecar": 1,
"nom_typecar": "Type17",
"id_car": 14,
"id_user": 3,
"cars": [
{
"id_car": 1,
"nom_car": "775",
}
},
{
"id_car": 2,
"nom_car": "048",
}
]
},
{
//Not displayed if user_id != 1 or null -> I want to display that
"id_typecar": 2,
"nom_typecar": "Type1",
"id_car": 13,
"id_user": 1, //expected output "id_user": null
"cars": [
{
"id_car": 11,
"nom_car": "123",
},
{
"id_car": 12,
"nom_car": "456,
},
{
"id_car": 17,
"nom_car": "789",
}
]
And my query :
return TypeCars::with('cars')
->leftJoin('cars_user', 'cars_user.id', '=', 'type_cars.id')
->where('cars_user.id' = 1)
->orWhereNull('cars_user.id')
->get()
->toJson();
I wanted to retrieve the data even if the id_user is not there yet because I must display types and cars. I added the leftJoin constraint with a where "null" or where cars_user.userid = ? but if user 3 chose one type of car that user 1 has not chosen (because it won't null or userid = 1), it would not display cars anymore.
Thank you!
EDIT :
Actual output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 2 Type2Car 13 |
| 2 Type2Car 13 |
| 3 Type3Car NULL |
+---------------------------------+
Expected output :
+---------------------------------+
| id_typecar name_typecar id_user |
+---------------------------------+
| 1 Type1Car 14 |
| 1 Type1Car NULL | <-- needed for the display
| 2 Type2Car 13 |
| 2 Type2Car 14 |
| 3 Type3Car NULL |
| 3 Type3Car NULL |
+---------------------------------+
If I don't have this line if the user 13 didn't specify any cars for this type it won't display since user 14 has chosen one
mysql sql laravel eloquent
mysql sql laravel eloquent
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
edited Jan 19 at 13:37
FlickaMin
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
asked Jan 19 at 12:03
FlickaMinFlickaMin
126
126
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16
|
show 4 more comments
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16
|
show 4 more comments
1 Answer
1
active
oldest
votes
Based on deleted answer, I solved my issue :
return TypeCars::with('cars')->leftJoin('user_cars', function($join){
$join->on('user_cars.id', '=', 'type_cars.id');
$join->on('user_cars.id', '=', DB::raw(1));
})
->whereNull('user_cars.id')
->where('user_cars.id', '=', 1)
->get()
->toJson();
DB::raw is needed to avoid Eloquent from quoting raw values.
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
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%2f54266886%2fsql-force-left-join-to-print-no-match%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
Based on deleted answer, I solved my issue :
return TypeCars::with('cars')->leftJoin('user_cars', function($join){
$join->on('user_cars.id', '=', 'type_cars.id');
$join->on('user_cars.id', '=', DB::raw(1));
})
->whereNull('user_cars.id')
->where('user_cars.id', '=', 1)
->get()
->toJson();
DB::raw is needed to avoid Eloquent from quoting raw values.
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
add a comment |
Based on deleted answer, I solved my issue :
return TypeCars::with('cars')->leftJoin('user_cars', function($join){
$join->on('user_cars.id', '=', 'type_cars.id');
$join->on('user_cars.id', '=', DB::raw(1));
})
->whereNull('user_cars.id')
->where('user_cars.id', '=', 1)
->get()
->toJson();
DB::raw is needed to avoid Eloquent from quoting raw values.
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
add a comment |
Based on deleted answer, I solved my issue :
return TypeCars::with('cars')->leftJoin('user_cars', function($join){
$join->on('user_cars.id', '=', 'type_cars.id');
$join->on('user_cars.id', '=', DB::raw(1));
})
->whereNull('user_cars.id')
->where('user_cars.id', '=', 1)
->get()
->toJson();
DB::raw is needed to avoid Eloquent from quoting raw values.
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
Based on deleted answer, I solved my issue :
return TypeCars::with('cars')->leftJoin('user_cars', function($join){
$join->on('user_cars.id', '=', 'type_cars.id');
$join->on('user_cars.id', '=', DB::raw(1));
})
->whereNull('user_cars.id')
->where('user_cars.id', '=', 1)
->get()
->toJson();
DB::raw is needed to avoid Eloquent from quoting raw values.
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
answered Jan 19 at 13:37
FlickaMinFlickaMin
126
126
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%2f54266886%2fsql-force-left-join-to-print-no-match%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
Does the JSON somehow represent sample data of what is currently in your tables? Can you post a more easy-to-interpret version (flat text)
– Caius Jard
Jan 19 at 12:53
I've updated with ASCII output about what is going on and what expected. It's pretty hard to explain, apologies if you don't understand, I'm trying to be as clear as possible but that's not easy
– FlickaMin
Jan 19 at 13:08
Why are you not complaining that type2car also has no null row?
– Caius Jard
Jan 19 at 13:14
I forgot to add it but that is the same for others in fact. I'm editing. Someone answered with an answer that was working very close from what I need but he unfortunaterly deleted it
– FlickaMin
Jan 19 at 13:15
So you basically always want a null row for each car as well as non null rows showing cars that are booked?
– Caius Jard
Jan 19 at 13:16