Jump to content

SQL_BIG_SELECT syntax


Recommended Posts

Hi there,

I need help with the correct syntax and where exactly to add the statement. I'm having issue with Stock management in Ps 1.7.3 and i need to add SQL_BIG_SELEC=1 before the select. It's not possible to change server settings. Below is the function with the query in it. I know i have to add one of those statement but dont know where to insert it. Actually shouldn't be SET SESSION... Too many unknows for me.

Db::getInstance()->execute('SET SQL_BIG_SELECTS=1');

Db::getInstance(_PS_USE_SQL_SLAVE_)->execute('SET SQL_BIG_SELECTS=1');

StockRepository.php

    protected function selectSql(
        $andWhereClause = '',
        $having = '',
        $orderByClause = null
    )
    {
        if (is_null($orderByClause)) {
            $orderByClause = $this->orderByProductIds();
        }

        return str_replace(
            array(
                '{left_join}',
                '{and_where}',
                '{having}',
                '{order_by}',
                '{table_prefix}',
            ),
            array(
                $this->joinLimitingCombinationsPerProduct(),
                $andWhereClause,
                $having,
                $orderByClause,
                $this->tablePrefix,

            ),
            'SELECT SQL_CALC_FOUND_ROWS
            p.id_product AS product_id,
           ......
          .......
            GROUP BY p.id_product, COALESCE(pa.id_product_attribute, 0)
            HAVING 1 {having}
            {order_by}
        ');
    }

check that link for the full code https://github.com/PrestaShop/PrestaShop/blob/develop/src/PrestaShopBundle/Entity/Repository/StockRepository.php

Edited by DanielaPetkova (see edit history)

Share this post


Link to post
Share on other sites

Hmm....first of all thank you for supporting. Second i did it but it seems there is some cache that i am missing to delete. In var/cache there is nothing , not even folders and i deleted class_index.php from prod and dev folders. Any new ideas ?

Share this post


Link to post
Share on other sites

It is a bit of a problem with Prestashop that it is half on Symfony and half not.

The first thing you can do is check that this function is really called in your case. A simple "die()" command will be enough for that.

There is also one other file that you can try: /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliConnection.php

Share this post


Link to post
Share on other sites

Even if i rename the .php file nothing has changed in back office. MysqliConnection.php or DbMySQLi.php. Everything is working normal in the BO even without both files. So they are not in use or there is some cache that i don't know. Continue searchin what is going on. Here is a screenshot of the error i'm fighting with.  Usually it happens after i added new product eve thow they are not a lot. After deleting and re-adding is all good but not now.

 

mm.png

Share this post


Link to post
Share on other sites

Yep , but function tryToConnect is not used in dbpdo.php also. There is 

 

    public function connect()
    {
        try {
            $this->link = $this->getPDO($this->server, $this->user, $this->password, $this->database, 5);
        } catch (PDOException $e) {
            throw new PrestaShopException('Link to database cannot be established: '.$e->getMessage());
        }

        $this->link->exec('SET SESSION sql_mode = \'\'');

        return $this->link;
    }

which is in use but still cannot make SQL_BIG_SELECT work.

Share this post


Link to post
Share on other sites

I could not manage to add SQL_BIG_SELECT before the query no matter what i tried so i went the other way. Try to find what is not ok with the query. It turns out that part [ {table_prefix} is replaced with my DB prefix. ]

...

    COALESCE(i.id_image, 0) AS combination_cover_id,
....

            LEFT JOIN ps4e_image i ON ( i.id_product = p.id_product AND
                COALESCE(FIND_IN_SET(i.id_image, images_per_combination.image_ids), 0) > 0
           )

 

If i comment out that part is working ok. I need to investigate more why is that.  It was working ok before i bet something wrong with the images went.

Share this post


Link to post
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...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More