SQLSTATE[23000]: Integrity constraint violation: 1052 using join with laravel
I have two tables and I am trying to join them in order to get all the referenced tables but getting errors but I am not sure which part I am doing wrong, can someone please give me a hand?
I have this table migration for tickets
public function up()
{
Schema::create('tickets', function (Blueprint $table) {
$table->increments('id');
$table->string('ticketNumber')->nullable(true)->unique();
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I have this table which saves notes for the tickets ticket_notes and I am referencing using the ticketNumber instead of the id
public function up()
{
Schema::defaultStringLength(191);
Schema::create('ticket_notes', function (Blueprint $table) {
$table->string('ticketNumber');
$table->foreign('ticketNumber')->references('ticketNumber')->on('tickets');
$table->increments('id');
$table->string('rloc', 50);
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I am usin query builder to do the query instead of eloquent relationships
DB::table('tickets')->where([
'ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=', 'ticket_notes.ticketNumber')
->select('tickets.*');
I am getting this error
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ticketNumber' in where clause is ambiguous (SQL: selecttickets.* fromticketsinner jointicket_notesontickets.ticketNumber=ticket_notes.ticketNumberwhere (ticketNumber= 12345 andis_deleted= 0))
Anyone can give me a fresh look where I did wrong with the join?
Thanks in advance for any suggestions / help.
php mysql sql laravel join
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
add a comment |
I have two tables and I am trying to join them in order to get all the referenced tables but getting errors but I am not sure which part I am doing wrong, can someone please give me a hand?
I have this table migration for tickets
public function up()
{
Schema::create('tickets', function (Blueprint $table) {
$table->increments('id');
$table->string('ticketNumber')->nullable(true)->unique();
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I have this table which saves notes for the tickets ticket_notes and I am referencing using the ticketNumber instead of the id
public function up()
{
Schema::defaultStringLength(191);
Schema::create('ticket_notes', function (Blueprint $table) {
$table->string('ticketNumber');
$table->foreign('ticketNumber')->references('ticketNumber')->on('tickets');
$table->increments('id');
$table->string('rloc', 50);
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I am usin query builder to do the query instead of eloquent relationships
DB::table('tickets')->where([
'ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=', 'ticket_notes.ticketNumber')
->select('tickets.*');
I am getting this error
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ticketNumber' in where clause is ambiguous (SQL: selecttickets.* fromticketsinner jointicket_notesontickets.ticketNumber=ticket_notes.ticketNumberwhere (ticketNumber= 12345 andis_deleted= 0))
Anyone can give me a fresh look where I did wrong with the join?
Thanks in advance for any suggestions / help.
php mysql sql laravel join
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53
add a comment |
I have two tables and I am trying to join them in order to get all the referenced tables but getting errors but I am not sure which part I am doing wrong, can someone please give me a hand?
I have this table migration for tickets
public function up()
{
Schema::create('tickets', function (Blueprint $table) {
$table->increments('id');
$table->string('ticketNumber')->nullable(true)->unique();
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I have this table which saves notes for the tickets ticket_notes and I am referencing using the ticketNumber instead of the id
public function up()
{
Schema::defaultStringLength(191);
Schema::create('ticket_notes', function (Blueprint $table) {
$table->string('ticketNumber');
$table->foreign('ticketNumber')->references('ticketNumber')->on('tickets');
$table->increments('id');
$table->string('rloc', 50);
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I am usin query builder to do the query instead of eloquent relationships
DB::table('tickets')->where([
'ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=', 'ticket_notes.ticketNumber')
->select('tickets.*');
I am getting this error
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ticketNumber' in where clause is ambiguous (SQL: selecttickets.* fromticketsinner jointicket_notesontickets.ticketNumber=ticket_notes.ticketNumberwhere (ticketNumber= 12345 andis_deleted= 0))
Anyone can give me a fresh look where I did wrong with the join?
Thanks in advance for any suggestions / help.
php mysql sql laravel join
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
I have two tables and I am trying to join them in order to get all the referenced tables but getting errors but I am not sure which part I am doing wrong, can someone please give me a hand?
I have this table migration for tickets
public function up()
{
Schema::create('tickets', function (Blueprint $table) {
$table->increments('id');
$table->string('ticketNumber')->nullable(true)->unique();
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I have this table which saves notes for the tickets ticket_notes and I am referencing using the ticketNumber instead of the id
public function up()
{
Schema::defaultStringLength(191);
Schema::create('ticket_notes', function (Blueprint $table) {
$table->string('ticketNumber');
$table->foreign('ticketNumber')->references('ticketNumber')->on('tickets');
$table->increments('id');
$table->string('rloc', 50);
$table->boolean('is_deleted')->default(false);
$table->timestamps();
});
}
I am usin query builder to do the query instead of eloquent relationships
DB::table('tickets')->where([
'ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=', 'ticket_notes.ticketNumber')
->select('tickets.*');
I am getting this error
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ticketNumber' in where clause is ambiguous (SQL: selecttickets.* fromticketsinner jointicket_notesontickets.ticketNumber=ticket_notes.ticketNumberwhere (ticketNumber= 12345 andis_deleted= 0))
Anyone can give me a fresh look where I did wrong with the join?
Thanks in advance for any suggestions / help.
php mysql sql laravel join
php mysql sql laravel join
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
asked Nov 24 '18 at 0:50
DoraDora
1,63252552
1,63252552
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53
add a comment |
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53
add a comment |
1 Answer
1
active
oldest
votes
Because of the join, there are 2 columns named ticketNumber. Specify which one you want.
'tickets.ticketNumber' => 12345
DB::table('tickets')->where([
'tickets.ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=',
'ticket_notes.ticketNumber')
->select('tickets.*');
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
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%2f53454276%2fsqlstate23000-integrity-constraint-violation-1052-using-join-with-laravel%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
Because of the join, there are 2 columns named ticketNumber. Specify which one you want.
'tickets.ticketNumber' => 12345
DB::table('tickets')->where([
'tickets.ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=',
'ticket_notes.ticketNumber')
->select('tickets.*');
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
add a comment |
Because of the join, there are 2 columns named ticketNumber. Specify which one you want.
'tickets.ticketNumber' => 12345
DB::table('tickets')->where([
'tickets.ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=',
'ticket_notes.ticketNumber')
->select('tickets.*');
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
add a comment |
Because of the join, there are 2 columns named ticketNumber. Specify which one you want.
'tickets.ticketNumber' => 12345
DB::table('tickets')->where([
'tickets.ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=',
'ticket_notes.ticketNumber')
->select('tickets.*');
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
Because of the join, there are 2 columns named ticketNumber. Specify which one you want.
'tickets.ticketNumber' => 12345
DB::table('tickets')->where([
'tickets.ticketNumber' => 12345,
'is_deleted' => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=',
'ticket_notes.ticketNumber')
->select('tickets.*');
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
answered Nov 24 '18 at 0:56
ryantxrryantxr
2,6691520
2,6691520
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
add a comment |
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
ah! thx thx, do really need the fresh eye. I know this isn't in the question and your answer solved it. But just another quick question if you are able to give me a hand. I realized after querying this gives me two arrays if there are two notes. Is it possible to combine them and return one object and having the notes as an array instead?
– Dora
Nov 24 '18 at 2:04
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
@Dora Please ask further questions in new posts, not comments.
– philipxy
Nov 24 '18 at 9:56
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%2f53454276%2fsqlstate23000-integrity-constraint-violation-1052-using-join-with-laravel%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
This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 24 '18 at 9:53