Level 46
What have you done so far? Share some code and we might be able to point you in the right direction.
Jul 10, 2017
6
Level 1
Just can't make an "Advanced Query" work
Hello everbody! I'm new here in Laracasts... I've been searching for an answer to my question for two or three days, and I can't be able to found it. So I have decided to make a question here in the forum (sorry for my bad, bad english...)
The problem is: I have a SQL Query that works perfectly if executed directly in the DataBase via PHPmyAdmin or any other SQL manager. But I just can´t do the right things to make it work in Laravel.
Here is the query and a short description of what I want:
SELECT *, COUNT(id_profile) AS total_votos FROM (SELECT * FROM likes GROUP BY id_profile) AS sql_likes INNER JOIN lanches ON lanches.id = id_lanche INNER JOIN users ON users.id = lanches.user_id GROUP BY id_lanche ORDER BY total_votos DESC
The app is for a thematic snackbar that serves hamburgers with Starwars and Star Trek themes, and they decided to make a "battle" so the custumers can create their own hamburgers with a custom recipe and etcetera.
The competition now is open for the public to voting and I find out a problem: if someone click repeatedly the "VOTE" button, the system receive multiple queries and now we have a lot of people having thousand of votes. I have blocked by IP, but if the clicks are so fast that the browser doesn't have time to send the request to the new page, the votes are stored anyway. There is a lot of solutions, but what I need here is to make a query that count only one vote per hamburger, and the query do that. So, the guests will see that the error was "revised" and will stop clicking the "VOTE" button.
The system has 3 specific tables to store the information about the user, the hamburger and the votes.
The table "users" stores the Users data. The table "lanches" stores the hamburgers data. The table "likes" stores the votes data.
I need to transport this query to laravel, but I just can't make the things to work properly.
Everytime I have a different error, and now my time is running out.
So... Anyone can help me out? Thank you so much!
Level 1
I've tried something I've read here in the forum...
$lanches = DB::select(DB::raw("SELECT *, COUNT(id_profile) AS total_votos FROM (SELECT * FROM likes GROUP BY id_profile) AS sql_likes
INNER JOIN lanches ON lanches.id = id_lanche
INNER JOIN users ON users.id = lanches.user_id
GROUP BY id_lanche
ORDER BY total_votos DESC
LIMIT 10"));
But got an error:
QueryException in Connection.php line 647: SQLSTATE[42000]: Syntax error or access violation: 1055 'spockburger.likes.id' isn't in GROUP BY (SQL: SELECT *, COUNT(id_profile) AS total_votos FROM (SELECT * FROM likes GROUP BY id_profile) AS sql_likes INNER JOIN lanches ON lanches.id = id_lanche INNER JOIN users ON users.id = lanches.user_id GROUP BY id_lanche ORDER BY total_votos DESC LIMIT 10)
Level 75
Does the person have to login to vote? That might help, with a "one time table" created that shows the users id after a vote is cast. If they try to vote again, and that id is already in the table, then they can't vote again.
Something like that.
Level 1
No... The votes are open to public... The system is storing the IP's + Browser Version in the likes table to guarantee that the votes are unique, but someone discover that if you click repeatedly times the "VOTE" button, the votes are stored without blocking... And now we have a ranking that cannot be reset, and the solution I found is to filter the queries and send a notification e-mail to the participants warning that if we count multiple votes coming from one IP one more time, the hamburger will be disqualified...
Level 1
Here is the link to the website, if can help: http://spockburger.com.br/batalha
Level 1
Guys, I found out the answer!! Thanks for the replies, but the problem was how the Laravel handle the GroupBy by default, and I changed the values in the database.php file, in the section:
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
Changed the 'strict' value to false, and everything seens to be OK!
1 like
Please or to participate in this conversation.