+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forum-6.html)
+--- Forum: Issues (https://forum.codeigniter.com/forum-19.html)
+--- Thread: Query builder subquery added multiple quotes if select more than 1 column (/thread-73852.html)
Pages:
1
2
Query builder subquery added multiple quotes if select more than 1 column - nata - 06-12-2019
Hi,
I use Postgress with Codeigniter V.3.1.10
I try to add subquery like this:
Code:
$this->db->select("pendaftaran_id, pendaftaran_tanggal");
$this->db->join("t_pendaftaran", "t_pendaftaran.pendaftaran_id = t_trx.pendaftaran_id");
$this->db->from("(SELECT trx_no, pendaftaran_id FROM t_transaksi GROUP BY trx_no, pendaftaran_id) as t_trx");
$q = $this->db->get();
return $q->result_array();and the result is
Code:
SELECT "pendaftaran_id", "pendaftaran_tanggal" FROM (SELECT trx_no, "pendaftaran_id FROM t_transaksi GROUP BY" "trx_no", pendaftaran_id) as t_trx
JOIN "t_pendaftaran" ON "t_pendaftaran"."pendaftaran_id" = "t_trx"."pendaftaran_id"RE: Query builder subquery added multiple quotes if select more than 1 column - php_rocs - 06-13-2019
@nata,
I don't understand your question. Are you receiving errors? Are you asking if what you have above is correct?
Here are some possible solutions:
- https://stackoverflow.com/questions/46107774/codeigniter-active-records-where-in-subquery
- Query Binding ( https://www.codeigniter.com/user_guide/database/queries.html#query-bindings ). This will allow you to do the following...
$sql = "SELECT pendaftaran_id, pendaftaran_tanggal
FROM (SELECT trx_no, pendaftaran_id FROM t_transaksi GROUP BY trx_no, pendaftaran_id) as t_trx
JOIN t_pendaftaran ON t_pendaftaran.pendaftaran_id = t_trx.pendaftaran_id";
$this->db->query($sql);
- Or you could create a database view (with the generated query in the database) and then call the view in CI.
RE: Query builder subquery added multiple quotes if select more than 1 column - nata - 06-13-2019
Hi, thanks for the answer @php_roccs,
Yes, I am receiving errors about the above subquery.
First, I don't want use $this->db->query();
Second, it is possible to do.
In this case, I want to make group by in some case, in $this->db->from(). The group by was success if I select 1 column, but more than 1 columns to be select, I got an error. The double quotes seem take in wrong place.
RE: Query builder subquery added multiple quotes if select more than 1 column - php_rocs - 06-13-2019
@nata,
if none of the suggested solutions are what you wish to use then my next recommendation is to troubleshoot the query to find out exactly where the issue is. Take the generated query that was created and run it directly in PostgreSQL. What error does it give you? Try to get the query to work in PostgreSQL and then mimic it in your CI code.
RE: Query builder subquery added multiple quotes if select more than 1 column - nata - 06-13-2019
(06-13-2019, 08:01 PM)php_rocs Wrote: @nata,
if none of the suggested solutions are what you wish to use then my next recommendation is to troubleshoot the query to find out exactly where the issue is. Take the generated query that was created and run it directly in PostgreSQL. What error does it give you? Try to get the query to work in PostgreSQL and then mimic it in your CI code.
@php_rocs,
My query in PostgreSQL is working fine. and I do the same in CI, the query's log is same as the query in PostgreSQL except the double quotes I mentioned above.
RE: Query builder subquery added multiple quotes if select more than 1 column - php_rocs - 06-13-2019
@nata,
Have you tried it with single quotes? To see if it makes a difference?
Also, have you tried making the query a view in the database and then calling the view in CI?
RE: Query builder subquery added multiple quotes if select more than 1 column - nata - 06-13-2019
(06-13-2019, 11:06 PM)php_rocs Wrote: @nata,
Have you tried it with single quotes? To see if it makes a difference?
Also, have you tried making the query a view in the database and then calling the view in CI?
Already tried single quotes but same errors.
if making query create in the view then call it in CI working fine.
RE: Query builder subquery added multiple quotes if select more than 1 column - hc-innov - 06-14-2019
Your issue with quotes.
I think that's the normal behavior of CI $this->db->from()
Look the fille system/database/DB_query_builder ligne 478. there is an explode function in the parameter of from.
After that, you have an array:
(SELECT trx_no
pendaftaran_id FROM t_transaksi GROUP BY
trx_no
pendaftaran_id) as t_tr
line 483: each row of the array go to function protect_identifiers...: you find it in DB_driver line 1782.
In this function you find line 1833 a condition: if the string contains a parenthesis or ' return the string without quotes.
Finally, you can't get the correct query....
RE: Query builder subquery added multiple quotes if select more than 1 column - php_rocs - 06-14-2019
(06-13-2019, 11:18 PM)nata Wrote:(06-13-2019, 11:06 PM)php_rocs Wrote: @nata,
Have you tried it with single quotes? To see if it makes a difference?
Also, have you tried making the query a view in the database and then calling the view in CI?
Already tried single quotes but same errors.
if making query create in the view then call it in CI working fine.
@nata,
Making sure I understand you. You made the query above in PostgreSQL and saved it as a view (Get_Penda ) in the database. You then called the view in CI like this...
PHP Code:
$this->db->select("pendaftaran_id, pendaftaran_tanggal");
$this->db->from("Get_Penda");
$q = $this->db->get();
return $q->result_array();
RE: Query builder subquery added multiple quotes if select more than 1 column - nata - 06-14-2019
(06-14-2019, 02:46 AM)hc-innov Wrote: Your issue with quotes.Sorry for late answer.
I think that's the normal behavior of CI $this->db->from()
Look the fille system/database/DB_query_builder ligne 478. there is an explode function in the parameter of from.
After that, you have an array:
(SELECT trx_no
pendaftaran_id FROM t_transaksi GROUP BY
trx_no
pendaftaran_id) as t_tr
line 483: each row of the array go to function protect_identifiers...: you find it in DB_driver line 1782.
In this function you find line 1833 a condition: if the string contains a parenthesis or ' return the string without quotes.
Finally, you can't get the correct query....
Oh yup, that's my problem.
Thanks for your help.