I have a model:
class MyModel extends Model
{
use HasFactory;
public function rel()
{
return $this->hasMany(MyModel2::class, 'rel_id', 'id');
}
public function part1() {
return $this->rel()->where('lvl', 1)->inRandomOrder()->take(8);
}
public function part2() {
return $this->rel()->where('lvl', 2)->inRandomOrder()->take(2);
}
public function all_relation()
{
return $this->part1()->union($this->part2());
}
}
In the controller I try to get the results like this:
$result = MyModel::with(['all_relation' => function($query) {
$query->orderByRaw('RAND()');
}])->where('fv', '>', 0)->where('url', $url)->first();
dd($result );
I have error SQLSTATE[HY093]: Invalid parameter number.
What is this related to? I started developing on Laravel 10, now I've updated to 12 and noticed this error. I don't know if it's related to the version. I can't figure out what's wrong, the number of records in the linked table is enough.
Most likely it has to do with the fact that the query expects something to be a number and instead it finds a string.
If you have a table with values like
and you do a query like
SELECT *
FROM table1
WHERE value = 1000;
When the database hits the Three Thousand record, it will fail with that error.
If you change your query to
SELECT *
FROM table1
WHERE value = '1000';
Then you should be good to go.
@Tray2 I checked the fields. There is no such thing. url has a string type, and fv is tinyint(4). I checked, the problem is in the union function, because if all_relation is replaced with part1 or part2 everything works.
@wim91 Unions require that the data types of each field matches, so if query 1 gives int, and query 2 gives string, then it will give that error.
The take(2) needs to be on the complete query not on the individual union.
So if you need a 8 from one and two from the other, you need to use more complex queries and sub queries.
@Tray2 It's strange. After all, I still have the same project on the old version of Laravel 10. I just launched it and looked at what was in it, and there was this construction, and it displayed grouped sorting for me, did they break something in version 12, or, on the contrary, fix it?
@wim91 @Tray2 This error message comes from a mismatch in the number of placeholder parameters and bound parameters in a prepared statement, not from data type issues.
As for why this happens, I don't know, but the code seems really unusual. The with() method is for loading relations, but it's used here with a method that returns a union. I don't even know what the resulting SQL statements will look like. I feel there must be a better and cleaner way to achieve what you want, possibly using query scopes.
@JussiMannisto Yes, I would also like to understand how to make it simpler. I already wrote that I tested this code in Laravel 10 version of the old project, and it worked without errors, but it appeared on version 12. Similar methods have been published many times on various developer forums, and I once took this idea from there.
@JussiMannisto Yes, you are correct.
Similar methods have been published many times on various developer forums, and I once took this idea from there.
I seriously doubt this, as it's a misuse of relations and the with() method. You must have misinterpreted what you saw.
Eloquent first loads the primary models in a single query. If with() conditions were given, the relations are loaded in secondary queries, using the keys received in the primary query. You can see that even in the "successful" SQL query you posted from Laravel 10, the table1_id key is only applied on the first half of the union query. It's going to give you wrong results because Laravel expects with() to receive a relation, not a query builder from union(), which yields two separate SELECT statements.
@ghabe I'm not talking about the argument data type. For the queries to make sense, the relations argument must resolve to relation(s) at the end. In the example you linked, posts is a valid relation. In the original post, part1 and part2 are relations. all_relation is not a proper relation as it returns a union of two statements. Eloquent isn't built to handle that, which is why the foreign keys aren't applied correctly and the compiled SQL doesn't make practical sense.
The fact that it seemed to compile into syntactically valid SQL in older versions is just a coincidence. It didn't make logical sense even then, as you can see from the SQL that was posted.
UPD.
If you remove take(2) from the model, everything works, but I need a limited number of records, how can I achieve this in the model?
I tried the same project on an old version of Laravel 10, everything works there. The code writing in the model and controller is the same. The database is the same, except for a couple of fields that should not affect the choice.
In Laravel 10, a successful query looks like this:
(select * from `table2` where `lvl` = 1 and `table2`.`table1_id` in (1) order by RAND() limit 8) union (select * from `table1` where `lvl` = 2 order by RAND() limit 2) order by RAND()
In laravel 12 the error query looks like this:
Connection: mysql, SQL:
select * from
(select *, row_number()
over (partition by `table2`.`table1_id` order by RAND())
as `laravel_row` from `table2` where `lvl` = 1 and `table2`.`table1_id` in (1)) as `laravel_table` where `laravel_row` <= 8 order by `laravel_row`)
Data from Debuger.
UPD
If you remove take() from the model. Then the query is like this:
(select * from `table2` where `lvl` = 1 and `table2`.`table1_id` in (1) order by RAND()) union (select * from `table2` where `lvl` = 2 order by RAND())
Please or to participate in this conversation.