Jump to content

How to JOIN a table with a different PREFIX using DBQuery()


Recommended Posts


I'm trying to create a query using DbQuery() and it seems that this class automatically sets the table prefix 'ps_' or whichever you have so you don't have to set it. For example:

$query = new DbQuery();
            $query->from('product', 'p');          
            $query->innerJoin('product_lang', 'pl', 'pl.id_product = p.id_product');
            $query->innerJoin('category_product', 'cp', 'cp.id_product = p.id_product');   


As you can see, you don't need to write  $query->from('ps_product', 'p'); It knows already the prefix to the tables.

My problem is that I am trying to make a Left Join to a table I created with a different prefix, or not using the preset prefix at all. Having done that maybe a mistake, I don't know, but usually, the tables I create in my Prestashop database, have a different prefix to indicate to myself those are my tables.

I know that there is something like '$addprefix = false' for DbQuery() but I can't find the documentation about it and where to set that false, and how to write the code about the table then.  I am sure this has an easy solution. I have tried something like this:

$query->leftJoin('frik_location', 'fl', 'fl.id_product = p.id_product AND fl.id_product_attribute = IFNULL(pa.id_product_attribute, 0)' , false);

With that false at the end, but it does not work.

So my table is called fik_location instead of ps_location (if i were to use the default prefix), anyone could help me with this?

Thanks a lot in advance!



Link to comment
Share on other sites

Ok, I'll answer myself. 

After finding DBQuery.php in classes/db in the server, I realize that there is no option to use tables with other prefixes, not for LeftJoin or for any other:

public function leftJoin($table, $alias = null, $on = null)
        return $this->join('LEFT JOIN `'._DB_PREFIX_.bqSQL($table).'`'.($alias ? ' `'.pSQL($alias).'`' : '').($on ? ' ON '.$on : ''));

One could override this and create a new leftJoin function that allows you to use a different prefix or just change the name of the table. 

I wonder if there is any other way to do this while using DbQuery() but I guess not.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...