Query builder subquery added multiple quotes if select more than 1 column

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"

@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/4610...n-subquery
- Query Binding ( https://www.codeigniter.com/user_guide/d...y-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.

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.

@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.

@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.

@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?

@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.

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....

@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(); 


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....

Sorry for late answer.
Oh yup, that's my problem.   

Thanks for your help.