Select object not quoting column names for PostgreSQL

[Corey Taylor]

I'm looking to use Aura.SqlQuery for preparing statements to pass to Aura.Sql.

I've converted a simply select query to test, and am running into a basic problem where column names are not quoted.  The QueryFactory is initialized with 'pgsql'.

The only identifiers that seem to be quoted as schema and table names. Is there some trick to make column names recognized?

I've tried wrapping the column names in quotes and leaving them bare.  Some of the column names have spaces and most are case sensitive (dealing with existing DB).

An example:

        $selectObject

            ->cols(array(

                "'SID' as sid",

                "'Site Name' as site_name"

            ))

            ->from("wss.dw-view-sustainability")

            ->where("'Parent' = ?", $parentSid);

This gives the statement string:

SELECT 'SID' AS sid, 'Site Name' AS site_name FROM "wss"."dw-view-sustainability" WHERE 'Parent' = :_1_ 

corey

[Paul Jones]

> On Jan 23, 2017, at 04:33, Corey Taylor <corey.t...@gmail.com> wrote:
>
> I'm looking to use Aura.SqlQuery for preparing statements to pass to Aura.Sql.
>
> I've converted a simply select query to test, and am running into a basic problem where column names are not quoted. The QueryFactory is initialized with 'pgsql'.
>
> The only identifiers that seem to be quoted as schema and table names. Is there some trick to make column names recognized?

The trick is that you have to include the dot for the query-builder to recognize that it's an identifier. (I don't recall if that's explicitly stated in the docs.)

So, you would want to try something like this:


$selectObject
->cols(array(
"dw-view-sustainability.SID as sid",

"'Site Name' as site_name"
))
->from("wss.dw-view-sustainability")

->where("dw-view-sustainability.Parent = ?", $parentSid);

For the column name with spaces, you're stuck; you still have to quote that identifier manually, since there's no (reasonable) way for the query builder to know it's an identifier.

Let me know if that helps!


--
Paul M. Jones
pmjo...@gmail.com
http://paul-m-jones.com

Modernizing Legacy Applications in PHP
https://leanpub.com/mlaphp

Solving the N+1 Problem in PHP
https://leanpub.com/sn1php

[Corey Taylor]

I see.  That does make sense.  I guess it's easier to just manually quote the identifiers than to manually type the table names in every location.

corey

[Steve Pavarno]

Yeah

I ended up creating a wrapper class where I could pass the columns in as a final parameter to from(), join(), joinLeft(), joinInner(). It automatically prefixes the table name assuming that all columns listed are from that table.

Saves me a ton of typing...