Cakephp 3 inner join with count and group by not working
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
$options = array(
'fields' => array(
'item_requirements.*',
'COUNT(`item_requirements`.`quantity_required`) as count'
),
'joins' => array(
'INNER JOIN `items` AS item_requirements ON `item_requirements`.`item_id` = `items`.`id`'
),
'group' => '`item_requirements`.`item_id`',
'contain' => array(
'items' => array('fields' => array('name', 'specification'))
)
);
$query = $this->Indents->ItemRequirements->find('all', $options);
Error: SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ...item_requirements`.`quantity_required`) AS "counts"" AS "COU... ^
the above error occurs.. any solutions to solve this..
postgresql cakephp cakephp-3.0 query-builder
edited Nov 22 '18 at 12:52
ndm
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
add a comment |
0
$options = array(
'fields' => array(
'item_requirements.*',
'COUNT(`item_requirements`.`quantity_required`) as count'
),
'joins' => array(
'INNER JOIN `items` AS item_requirements ON `item_requirements`.`item_id` = `items`.`id`'
),
'group' => '`item_requirements`.`item_id`',
'contain' => array(
'items' => array('fields' => array('name', 'specification'))
)
);
$query = $this->Indents->ItemRequirements->find('all', $options);
Error: SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ...item_requirements`.`quantity_required`) AS "counts"" AS "COU... ^
the above error occurs.. any solutions to solve this..
postgresql cakephp cakephp-3.0 query-builder
edited Nov 22 '18 at 12:52
ndm
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
Have you tried replacing the aliascountby someting less ambigious ?
– code-kobold
Nov 23 '18 at 8:55
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00
add a comment |
0
0
0
$options = array(
'fields' => array(
'item_requirements.*',
'COUNT(`item_requirements`.`quantity_required`) as count'
),
'joins' => array(
'INNER JOIN `items` AS item_requirements ON `item_requirements`.`item_id` = `items`.`id`'
),
'group' => '`item_requirements`.`item_id`',
'contain' => array(
'items' => array('fields' => array('name', 'specification'))
)
);
$query = $this->Indents->ItemRequirements->find('all', $options);
Error: SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ...item_requirements`.`quantity_required`) AS "counts"" AS "COU... ^
the above error occurs.. any solutions to solve this..
postgresql cakephp cakephp-3.0 query-builder
edited Nov 22 '18 at 12:52
ndm
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
$options = array(
'fields' => array(
'item_requirements.*',
'COUNT(`item_requirements`.`quantity_required`) as count'
),
'joins' => array(
'INNER JOIN `items` AS item_requirements ON `item_requirements`.`item_id` = `items`.`id`'
),
'group' => '`item_requirements`.`item_id`',
'contain' => array(
'items' => array('fields' => array('name', 'specification'))
)
);
$query = $this->Indents->ItemRequirements->find('all', $options);
Error: SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near """" LINE 1: ...item_requirements`.`quantity_required`) AS "counts"" AS "COU... ^
the above error occurs.. any solutions to solve this..
postgresql cakephp cakephp-3.0 query-builder
postgresql cakephp cakephp-3.0 query-builder
edited Nov 22 '18 at 12:52
ndm
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
edited Nov 22 '18 at 12:52
ndm
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
edited Nov 22 '18 at 12:52
ndm
44.8k75493
edited Nov 22 '18 at 12:52
ndm
44.8k75493
edited Nov 22 '18 at 12:52
ndm
44.8k75493
44.8k75493
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
asked Nov 22 '18 at 10:59
Meenatchi PMeenatchi P
259
259
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
Have you tried replacing the aliascountby someting less ambigious ?
– code-kobold
Nov 23 '18 at 8:55
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00
add a comment |
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
Have you tried replacing the aliascountby someting less ambigious ?
– code-kobold
Nov 23 '18 at 8:55
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
Have you tried replacing the alias
count by someting less ambigious ?– code-kobold
Nov 23 '18 at 8:55
Have you tried replacing the alias
count by someting less ambigious ?– code-kobold
Nov 23 '18 at 8:55
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00
add a comment |
1 Answer
1
active
oldest
votes
0
The issue is with
item_requirements.*.
.* is not supported by cake ORM.
You can use ->autoFields(true) option. Refer this answer
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
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
});
}
});
draft saved
draft discarded
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%2f53429470%2fcakephp-3-inner-join-with-count-and-group-by-not-working%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
0
The issue is with
item_requirements.*.
.* is not supported by cake ORM.
You can use ->autoFields(true) option. Refer this answer
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
add a comment |
0
The issue is with
item_requirements.*.
.* is not supported by cake ORM.
You can use ->autoFields(true) option. Refer this answer
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
add a comment |
0
0
0
The issue is with
item_requirements.*.
.* is not supported by cake ORM.
You can use ->autoFields(true) option. Refer this answer
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
The issue is with
item_requirements.*.
.* is not supported by cake ORM.
You can use ->autoFields(true) option. Refer this answer
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
answered Nov 29 '18 at 10:03
RogerSSDRogerSSD
917
917
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53429470%2fcakephp-3-inner-join-with-count-and-group-by-not-working%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
Can you include the actual SQL query that this generates?
– Greg Schmidt
Nov 22 '18 at 18:01
query select BB.id item_id, BB.code || '-' || BB.sub_code || '-' || BB.name || '-' || BB.specification item_details, AA.quantity_required consolidated_quantity from (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b) BB on BB.id=AA.item_id ;
– Meenatchi P
Nov 23 '18 at 7:12
Have you tried replacing the alias
countby someting less ambigious ?– code-kobold
Nov 23 '18 at 8:55
Is the query you've shared here exactly what it generates? I don't see how all the AA and BB came to be, from the code you posted. If you've made edits before posting, those might accidentally change something.
– Greg Schmidt
Nov 23 '18 at 16:14
This is the query for the above case (select a.item_id, sum(a.quantity_required) quantity_required from tms.item_requirements a group by a.item_id) AA inner join (select b.id, b.code, b.sub_code, b.name, b.specification, b.unit_qty_code_id from tms.items b)
– Meenatchi P
Nov 24 '18 at 13:00