Strange Error with Laravel DB query

Did you run php artisan migrate on your production server, this can happens if you added the column later in the development, and it was not migrated in the development server, please check in your mysql client (phpmyadmin , sequelpro...) if the column actually exists in the database, before we explore other options

I looks like you are missing a column called is_featured in your table called contents.

Thanks for both responses. I wish that was the case but when I run the query in the error message on the DB directly it works without any issues.

(I have now added more details to the question - sorry for the confusion).

The "is_featured" column is generated via DB::raw (I have just included the php code). It is generated on the fly, but for the sake of simplicity I have changed it to just always return 0 here.

Any idea why the SQL would work on workbench but not when generated and run by Laravel?

Answer copied from stackoverflow

As is the case with most relational database systems, the SELECT clause is processed after the GROUP BY clause. This means you cannot use columns aliased in the SELECT (such as ts in this example) in your GROUP BY.

There are essentially two ways around this. Both are correct, but some people have preference for one over the other for various reasons.

First, you could group by the original expression, rather than the alias. This results in duplicate code, as you will have the exact same expression in both your SELECT and GROUP BY clause.

Hi Ahmed - I thought the exact same thing initially (when the query was more complicated and actually did have a group by).

Now in its simplest form, all the SQL is doing is adding a column to the inner query, then sorting by it in the outer query. There is no grouping at all.

Also, if this were the issue, surely workbench would also complain (as would my development environment)?

Thanks!

@REZAMK - The example contained grouping, but it also applies to sort by, try the two solutions provided on stackoverflow, for your sort by.

Happy to accept if I have made a mistake somewhere, but it looks to me like I am already doing exactly what is suggested in the 2nd option. I have an internal query, called c, and I am sorting by a column in here?

How would you suggest the query is changed? Simplified even further...

SELECT `c`.* 
FROM   (SELECT 0 AS `is_featured`, `contents`.* FROM   `contents`) AS `c` 
ORDER  BY `c`.`is_featured` DESC 

Again, this query works fine in workbench but not when run on my UAT box in php. It also runs fine on the development machine... which may provide a clue.

To avoid any doubt, I tried this:

return DB::select(" SELECT `c`.* 
                    FROM   (SELECT 0 AS `xxx`, u.* FROM   `users` u) AS `c` 
                    ORDER  BY `c`.`xxx` DESC");

The users table is unchanged from the one shipped with laravel auth so nothing special here.

I have also removed all eloquent references.

And the error:

(3/3) QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.xxx' in 'order clause' (SQL: SELECT `c`.* 
FROM (SELECT 0 AS `xxx`, 
u.* 
FROM `users` u) AS `c` 
ORDER BY `c`.`xxx` DESC)

Edited the question to use this example instead.

Any help would be appreciated.

Another finding... When I run the below code on my UAT environment, it works fine. Suggesting Laravel is doing something internally?

// Create connection  
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT c.* FROM (SELECT 0 AS xxx, u.* FROM users u)  c ORDER BY c.xxx DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["id"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();

Hey rezamk,

have you found any solution to this problem?