Level 70
@dougd_nc It's because of this:
You cannot use a named parameter marker of the same name twice in a prepared statement
Feb 20, 2023
8
Level 1
Why does Query Builder not allow named parameters?
This may be an unanswerable question lol, but does anybody know why Query builder is designed to not allow named query parameters??! I was trying to get them to work for a while, then saw this code in Illuminate/Database/Query/Builder.php
public function getBindings()
{
return Arr::flatten($this->bindings);
}
If I give bindings such as
[
':access' => Group::ACCESS_MEMBER,
]
It will just get rid of the indexes.
Level 102
It would require a rewrite of eloquent from scratch as it would need to figure out the name of each parameter to insert in the string based on the array. And it is related to what @tisuchi said if you ever try to use multiple tables on a where exists or similar
Also it would often be a mix. Imagine a where in with 50 ids.
1 like
Level 1
@Sinnbeck ok. The name is already set in the array, and I was not using a name twice. And using the question marks is directly equivalent to using named parameters with numbers at the end, for example, param1, param2 etc. They already know how to get the question marks in the right order, so assigning parameters to values isn't a problem.
Well, must have something to do with some code intricacies that i am unfamiliar with. Unless I have a simple example of what can't be done with named parameters, I'll have to look at the Eloquent code and how they use parameters in order to follow what you are saying. I do get that there is some existing code that this will apparently somehow break though.
Level 1
@jlrdw This is not the real query, but something similar:
$people = UserAccounts::whereRaw("acct_id IN
(SELECT a.acct_id
FROM test_group g, test_acl a
WHERE a.groupid=g.groupid
AND a.groupaccess>= :access
AND g.groupid= :groupid
AND a.status >= :status
AND (g.site = :sitecode or g.site='*')
AND (a.expiredate > CURRENT_TIMESTAMP or a.expiredate is NULL)
AND a.is_group='N')")->setBindings(
[
'access' => Groups::ACL_ACCESS_MEMBER,
'groupid' =>564,
'status' => Groups::ACL_STATUS_APPROVED,
'sitecode' => $siteCode
]);
The main thing is, I want to use a raw where clause with named parameters for something I'm doing.
Level 75
@dougd_nc for that consider the db facade or getPdo(). An example:
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Edit:
For some complex reports I generally use the PDO instance (getPdo), and I have setup a custom length aware pagination class if pagination is needed.
Level 1
@jlrdw Ok thanks. I'll take a look at that.
Please or to participate in this conversation.