Level 39
Do you get results from the following?
Country::all()
If so, then it would seem that you're filtering in such a way that no matches are found. If not, then perhaps the Country model can't be found.
Dec 13, 2022
12
Level 7
OderByRaw not returning results, but working fine elsewhere for similar query
I am trying to get "Top 10" results for travel destinations: Countries, Cities, Things to Do. I am using the query builder and OrderByRaw to order destinations by "Upvotes - Downvotes" so the top 10 user voted destinations in each category will be fetched from the DB.
This is my basic query :
Destination::where('type', '=', 'destination_type')->orderByRaw('(upvotes - downvotes) desc')->take(10)->get();
I have Classes for each type of Destination: Country, City, PointOfInterest (for things to do). So my Country specific query actually looks like:
Country::where('type', '=', 'country')->orderByRaw('(upvotes - downvotes) desc')->take(10)->get();
Basicaly the same query is used for each class and I just swap out the Class name and 'type' to reflect such.
All destinations live in the same DB table. All have the same columns, all have Upvotes and Downvotes, although many values in these 2 columns are still '0', but not specifically 'NULL'.
Both Cities and Points Of Interest Top 10 results are being fetched and displaying as expected. But the Country results are coming back as NULL. Literally nothing is being fetched. I definitely have countries and some countries have vote values in the respective columns.
If I remove the OrderByRaw clause from the Country query, i can fetch ALL countries. But with the clause I get a NULL set.
I have confirmed no typos in the clause, I have swapped the 'type' value to be 'city' instead of country, both the (exact same) city and points of interest queries work. I have tried a raw SQL query in my DB and can retrieve the expected results. But I can't fetch the results from within my app for Countries.
Any advice?
Level 7
@lbecket Country:all() does work. And there are definitely matches for the results I am expecting with
Country::where('type', '=', 'country')->orderByRaw('(upvotes - downvotes) desc')->take(10)->get();
But nothing is being returned.
As mentioned in my original post, If I put the Raw SQL into my DB's SQL reader, the expected results are fetched, so this works in PHPMyAdmin:
SELECT * FROM destinations WHERE type = country ORDER BY 'upvotes' - 'downvotes' DESC LIMIT 10
From that query I get the 10 top voted countries from my DB. My issue is the Eloquent version of this query. And the Eloquent query works when I remove the orderByRaw clause, but i need that so I grab the top 10 out of the 50+ countries I have in the DB.
Level 39
I suspect that the issue is with your orderByRaw clause. When I try this (with different data admittedly) I get the error SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range but removing the orderByRaw allows it to work. If yours works in Eloquent without the order clause, then you might consider restructuring the query... perhaps a sub-query where the difference in up/down votes is pre-calculated so that the ordering is done on a field, not a calculation.
Level 7
@lbecket The issue does seem to be with the OrderByRaw clause. It works on other classes with the same query and same data:
City::where('type', '=', 'country')->orderByRaw('(upvotes - downvotes) desc')->take(10)->get();
PointOfInterest::where('type', '=', 'country')->orderByRaw('(upvotes - downvotes) desc')->take(10)->get();
Bot work and grab the top 10 results as expected. They are all 3 (Country, City, PointOfInterest) using the same data on the same table (destinations) and the same columns.
When I created the DB originally I had a "total_votes" field, but thought it would be simpler to let the app calc the votes from the upvotes and downvotes fields. I'm not sure why this is not working just for the countries in the table.
Assuming the data for up/down votes for each type od destination is accurate and the values are in fact Integers (including 0s) within the 'range' (they are all 0-1000), any idea why only the Country types would not work when City and PointOfInterest fetch data fine?
Also, I'm not sure i fully understand the solution you are proposing.
Level 28
What is the output if you dd the SQL?
$sql = Country::where('type', '=', 'country')->orderByRaw('(upvotes - downvotes) desc')->take(10)->toSql();
dd($sql);
Level 7
@drgreen NULL
But this provides the first 10 countries in the table:
$sql = Country::where('type', '=', 'country')->take(10)->toSql();
dd($sql);
Note i removed the OrderByRaw clause.
Level 39
@Scotalia What I was suggesting earlier is that you essentially make what you have done here the sub-query with a calculated field for the difference in up/down votes. If that calculated field were called, say, net_votes, then the outer query can orderBy('net_votes', desc). Alternatively, you could just use what you have here and then sort the returned collection.
You also mentioned that all votes were integers between 0 and 1,000, but you might want to double-check that there aren't any missing or null values. You said that everything is the same, but the results indicate otherwise, so it couldn't hurt to take a closer look.
Level 28
@Scotalia Hmm, is it? I'm not sure why it is returning null. The toSql() method should return the SQL query itself, not the result.
What is the $table value in your Country model? Is it assigned as 'destinations'?
$table = 'destinations';
Level 7
@lbecket Thanks for the clarification.
I did check the DB and specifically each value for the 'upvotes' and 'downvotes' fields for each country to confirm the data is either an actual '0' or a INT. And they all are.
I agree there must be something wrong with the data for Country items, but I can't figure out what it is.
Level 7
@drgreen Sorry, the dd result is actually
select * from `destinations` where `type` = ? order by (upvotes - downvotes) desc limit 10
The place holder (?) SHOULD reference 'country', but not sure if there is a problem there.
And, Yes I have assigned it in the Model to refer to 'destinations' as the target table
// specifiy the parent DESTINATIONS table
protected $table = 'destinations';
Level 7
@lbecket I've been tinkering with the query abit more and it seems to specifically be the 'upvotes - downvotes' calculation in the query specifically for Country class.
Both of the following return results, but not when i include 'upvotes - downvotes'
Country::where('type', '=', 'country')->orderByRaw('upvotes desc')->take(10)->get();
Country::where('type', '=', 'country')->orderByRaw('downvotes desc')->take(10)->get();
Not sure why this calc works for City and pointOfInterest classes but not Country, when the data is the same and in the same table. At least the data in the 'upvotes' and downvotes' columns SEEMS to be the same (ie proper INTs and not NULL or empty or less than 0)
Level 7
Since I could not get the query to work with the calculation of 'upvotes - downvotes', in the end I just scrapped the downvotes field and am using just 'upvotes' as my OrderBy:
Country::where('type', '=', 'country')->orderByRaw('upvotes desc')->take(10)->get();
It gets the job done, but is not 100% accurate to what I was hoping to display and how I was hoping to display it. Oh well.
Please or to participate in this conversation.