Is it possible to concatenate fields in a $query->select($db->quoteName(array(...?

[Nico van de Kamp]

Hello,

I know how to use CONCAT in MySQL itself. I have used it before.

Now I'm creating a field and I want to concatenate some fields like frontname,   , surname. Till know I'm using the function quoteName and put them in an array by default.

But is it than possible to concatenate fields like, this is not working but just to give an idea what I mean/want:

$query->select($db->quoteName(array('a.id', concatenate('a.fnam', '', 'a.bnam', ' ', 'a.lnam') 'as name' ),array('id','name')));

I can't believe that such is not possible. I have also used CONCAT, put everything in single quotes and escape the single quote's inside the concatenate like 'concatenate(\'.a.fnam\'...) as name'

Nico

[Mathew Lenning]

I generally don't use JDatabase::quoteName, because IMO in most instances it isn't needed.

 If you have special characters or your name fields use reserved words then maybe, but doing either of those is bad practice anyway so just don't and avoid the issue all together.

I do use CONCAT a lot and it works just fine with the Joomla query object.  

Here's how I'd write your select query

$query->select('a.id, CONCAT(a.fname, " ", a.bname, " ", a.lname) AS name'); 

Hope this helps. 

[João Serina]

Hello Nico,

I think that you are looking for the concatenate method of the Joomla Query Object (https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_concatenate).

The basic use is something like:

$query->select($query->concatenate($db->quoteName(array("a.fnam", "a.bnam", "a.lnam")), " ") . " AS name")

•  "$db->quoteName(array("a.fnam", "a.bnam", "a.lnam"))" are the columns you want to concatenate
•  " " is the string that is used to separate each column
  

Alternatively, you can write the SQL directly into the select method:

$query->select("CONCAT(" . $db->quoteName(array("a.fnam", "a.bnam", "a.lnam")) . " SEPARATOR ' ') AS name"); 

Keep in mind that you can have multiple select statements so you can separate the concatenated "name" select from the "id" select.

Best regards,

João Serina

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/8ceff08a-3ef2-4f9a-a7f9-14d856bed17co%40googlegroups.com.

[ni...@nicovandekamp.nl]

Hello,

Thanks Mathew, João.

The next example is doing the right thing for me:

• $query->select($query->concatenate($db->quoteName(array("a.fnam", "a.bnam", "a.lnam")), " ") . " AS name")

The other two with CONCAT are unfortunately not working for me:

• $query->select('a.id, CONCAT(a.fname, " ", a.bname, " ", a.lname) AS name');
  

• $query->select("CONCAT(" . $db->quoteName(array("a.fnam", "a.bnam", "a.lnam")) . " SEPARATOR ' ') AS name");

I become now concious or aware that let say the part "AS name" is doing the same thing as the last part of my select with the array" , array('id','name')))".

My question is if the alias 'AS ...' is doing the thing what is advantage of that second array with the  'alias' names?

Another question that I have is, although this working for me now, but I have now tow query->select statements: one for the id and one for name. I don't see at the moment but can combine these one or is that not possible? Just to know for in the future.

Nico

[João Serina]

Hello Nico,

to answer your questions:

My question is if the alias 'AS ...' is doing the thing what is advantage of that second array with the  'alias' names?

The Joomla Database Object method quoteName (https://api.joomla.org/cms-3/classes/JDatabaseDriver.html#method_quoteName) can receive one or two arguments / parameters. The first can be either a string with the name of a column / table or an array of strings with the names of columns / tables. The second ,is optional and, is a string or an array of strings with the aliases of the column / table names. This is mostly syntactic sugar when compared with the "AS ..." way of writing.. this will also automatically quote the strings which is recommended in most cases for safety / security.

So, essentially there is no difference in the end result, just on the readability of the code / query.

 Another question that I have is, although this working for me now, but I have now tow query->select statements: one for the id and one for name. I don't see at the moment but can combine these one or is that not possible? Just to know for in the future.

I am not sure if you could combine both select statements into one because you would be feeding  $db->quoteName()   an array with a string ("a.id") and a select group statement ("$query->concatenate($db->quoteName(array("a.fnam", "a.bnam", "a.lnam")), " ") . " AS name" "). Nonetheless you can try this out:

• $query->select($db->quoteName(array("a.id", $query->concatenate($db->quoteName(array("a.fnam", "a.bnam", "a.lnam")), " ")), array("id", "name")))

There is no problem with having multiple select statements in a query... maybe you'd get a very marginal benefit in combining them all but having code that is easy to read / understand / maintain is also a major consideration... especially if you're not working alone.

I hope this helps 😉

Best Regards,

João Serina

To view this discussion on the web, visit https://groups.google.com/d/msgid/joomla-dev-general/7e898edc-b48d-4e3d-a954-035415d3aeb0n%40googlegroups.com.

[ni...@nicovandekamp.nl]

Hello João,

Thanks for you're explanation. Single select statement is not working in this case, so will I leave it as it is, which means multiple select statements.

It is not an issue now, I will investigate it later, I like to find out right syntax, which can help me later.

Best Regards,

Nico