[odb-users] Concatenation of columns

Boris Kolpackov boris at codesynthesis.com
Wed Dec 12 08:46:57 EST 2012


Hi Reza,

In the future please keep your replies always CC'ed to the odb-users
mailing list as discussed in the posting guidelines:

http://www.codesynthesis.com/support/posting-guidelines.xhtml

Reza Jahanbakhshi <reza.jahanbakhshi at gmail.com> writes:

> I understand your concern about database server resources. Here is my
> scenario. I have a table which three of its columns each represent a
> meaningful entity and concatenated together represent a new entity. I need
> to use this new entity in queries specially in WHERE LIKE clause  because I
> need to filter on that. I think it's not efficient to fetch and iterate the
> whole table over network to find the rows I need. Also I think database
> engines can do this much better in terms of at least less memory usage and
> network traffic. Please tell me if I'm wrong and what is your suggestion
> for this kind of use-cases?

No, fetching all the rows and then doing the filtering on the client
side is definitely not the way to do it. The best way would be to make
concatenation unnecessary. That is, re-implement your LIKE clause to
use individual columns instead. But I agree, this may not always be
possible or convenient and sometimes it might be just simpler to
let the database do the work.

I am, however, still not convinced that we should provide wrappers
for operations like concatenation in ODB. Once we start on this path,
it will be hard to stop until we wrap pretty much all the operators,
and that's going to be a lot of them. Also, this "computation on the
database" approach really goes against the ODB (and ORM in general)
philosophy of treating the database as, well, a database rather than an
application server. I also realize that the real world is more complex
and often the easier or, in fact, the only practical way is to do some
computation on the database side. For such cases, however, I think the
native query support that ODB provides should be sufficient. Especially,
since such usage almost always leads to tight dependence on a specific
database system anyway.

Having said that, nothing prevents you from writing a few simple wrappers
that abstract away from specific databases, if you need to be portable.
For example:

#include <odb/mysql/query.hxx>
#include <odb/oracle/query.hxx>
#include <odb/mssql/query.hxx>

// MySQL version.
//
template <typename T, odb::mysql::database_type_id ID>
odb::mysql::query_base
concat (const odb::mysql::query_column<T, ID>& x,
        const odb::mysql::query_column<T, ID>& y)
{
  return "CONCAT(" + x + ",' '," + y + ")";
}

// Oracle version.
//
template <typename T, odb::oracle::database_type_id ID>
odb::oracle::query_base
concat (const odb::oracle::query_column<T, ID>& x,
        const odb::oracle::query_column<T, ID>& y)
{
  return x + "|| ' ' ||" + y;
}

// SQL Server version.
//
template <typename T, odb::mssql::database_type_id ID>
odb::mssql::query_base
concat (const odb::mssql::query_column<T, ID>& x,
        const odb::mssql::query_column<T, ID>& y)
{
  return x + "+ ' ' +" + y;
}

And then, in your code:

query q (concat (query::first, query::last) + "LIKE" + query::_val ("J% Doe"));

Boris



More information about the odb-users mailing list