Table names when using aliases

[KnightAR]

When using tables with aliases IE: SELECT * FROM users AS user lada-cache returns the table as users AS user; The table name should actually be modified to use the real database table so that the tags are correctly wrote and cleared. I've recently started having issues with specific queries and noticed this bug.

I have fixed it locally by adding a preg_replace. Here are my changes in the Reflector class:

    /**
     * Returns all affected tables, including joined ones.
     *
     * @return array
     */
    public function getTables()
    {
        // Get main table
        $tables = [];
        if (is_string($this->queryBuilder->from)) {
            $tables[] = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $this->queryBuilder->from));
        }

        // Add possible join tables
        $joins = $this->queryBuilder->joins ?: [];
        foreach ($joins as $join) {
            if (!in_array($join->table, $tables) && is_string($join->table)) {
                $tables[] = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $join->table));
            }
        }

        $this->getTablesFromWhere($this->queryBuilder, $tables);

        return $tables;
    }

    /**
     * Get Table Names From Where Exists, Not Exists (whereHas/whereDoesnthave builder syntax)
     *
     * @param QueryBuilder $queryBuilder
     */
    private function getTablesFromWhere(QueryBuilder $queryBuilder, &$tables) {
        if (!isset($queryBuilder->wheres)) {
            return;
        }
        $wheres = $queryBuilder->wheres ?: [];
        foreach ($wheres as $where) {
            if ($where['type'] == 'Exists' || $where['type'] == 'NotExists') {
                $table = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $where['query']->from));
                if (!in_array($table, $tables) && is_string($table)) {
                    $tables[] = $table;
                }

                // Add possible join tables
                $joins = $where['query']->joins ?: [];
                foreach ($joins as $join) {

                    if (!in_array($join->table, $tables) && is_string($join->table)) {
                        $tables[] = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $join->table));
                    }
                }
            }
            if (isset($where['query'])) {
                $this->getTablesFromWhere($where['query'], $tables);
            }
        }
    }

    /**
     * Returns all affected rows as a multidimensional array, split up by table.
     *
     * @return array
     */
    public function getRows()
    {
        $rows = [];
        $wheres = $this->queryBuilder->wheres ?: [];

        foreach ($wheres as $where) {

            // Skip unsupported clauses
            if (!isset($where['column'])) {
                continue;
            }

            // If it doesn't contain the table name assume it's the "FROM" table
            if (strpos($where['column'], '.') === false) {
                $where['column'] = implode('.', [trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $this->queryBuilder->from)), $where['column']]);
            }

            list($table, $column) = $this->splitTableAndColumn($where['column']);

            // Make sure that the where clause applies for the primary key column
            if ($column !== $this->queryBuilder->model->getKeyName()) {
                continue;
            }

            // Initialize a set for the current table
            if (!isset($rows[$table])) {
                $rows[$table] = [];
            }

            // Add the rows to the current table set
            if ($where['type'] === 'Basic') {
                if ($where['operator'] === '=' && is_numeric($where['value'])) {
                    $rows[$table][] = $where['value'];
                }
            }
            else if ($where['type'] === 'In') {
                $rows[$table] += $where['values'];
            }
        }

        return $rows;
    }

This is for the Laravel 5.x branch and may need to be tweaked for 6.x. I have not upgraded to 6 yet, so I'm not able to put a PR as of this moment.

[spiritix]

Thanks for the fix. Can you share a query (the PHP code, demonstrating the Eloquent / DB facade usage) where you use aliases? We generally don't support raw queries, I will need to look into possible use cases.

[KnightAR]

Using any ->join syntax: \App\Engines::join('cars as car', 'car.id', '=', 'engines.car_id')->get();