Asked  1 Year ago    Answers:  5   Viewed   12 times

I Got a problem with orderByRaw Laravel.

Array:

$arr = [H123456, H7654321];

query:

$ids = implode(',', $arr);
$query = User::whereIn('id', $arr)->isActive()->orderByRaw(DB::raw("FIELD(id,". $ids.")"))->get();

This is the raw query:

"select * from `merchant_heads` where `id` in (?, ?) and `category_id` = ? and `status` = ? order by FIELD(id,Hf561b6fd32aec6ea,H7c81e6fa3f85fc74) limit 10 offset 0"

I've already put this inside my User model:

public $incrementing = false;

When i execute the query it says Column not found: 1054 Unknown column 'Hf561b6fd32aec6ea' in 'order clause'.

I've tried to change $ids with single value like 1 it working. but it's not working if the ID is string like mine.

Any solution?

 Answers

3

Since the ids are strings, you need to encapsulate them. Luckily, laravel can do it for you. Try with this:

$ids = implode(',', $arr);
$qs = array_fill(0,count($arr),'?');
$query = User::whereIn('id', $arr)->isActive()->orderByRaw(DB::raw("FIELD(id,". implode(',', $qs).")"),$arr)->get();
Saturday, May 29, 2021
 
Markol
 
3

Tools like XAMP, MAMP or WAMP have their own PHP and MySQL binaries and do not use a potential globally installed version.
I guess you are using MySQL installed with XAMP, so the socket is not the default one that you could search in your system but the one in XAMP.

You have two options:

  1. Update your global PHP's php.ini to set the pdo_mysql.default_socket or mysqli.default_socket to the MySQL socket in your XAMP installation in order to your global PHP binary will be able to connect the your XAMP's MySQL version.
  2. Use the XAMP's PHP binary (recommanded).

The recommanded option is the second one, because the XAMP's PHP binary is already configured to use the right MySQL socket and does not need to update any configuration file.

I explain below how to find the MySQL socket and how to locate the PHP binary directory.

Find the MySQL socket path:

  • Write the following code in you Web server directory (usually www/, htdocs/ or public_html/)

    <?php
    //phpinfo.php
    phpinfo();
    
  • Open the URL http://localhost/phpinfo.php in your Web browser (adapt the host name if required).

  • According to the PHP extension you use to connect to the datase search for either pdo_mysql.default_socket or mysqli.default_socket and you'll find the path to the socket file.

See phpinfo().

Find the PHP binary directory:

  • Write the following code in your Web server directory

    <?php
    //bindir.php
    echo PHP_BINDIR;
    
  • Open the URL http://localhost/bindir.php in your Web browser.

  • The path to the directory hosting the PHP binary is written.

Thanks to this answer for the information.
See the documentation about pre-defined constants.

In your case the directory hosting PHP is /Applications/XAMPP/bin/ so you should run the command:

/Applications/XAMPP/bin/php artisan migrate
Thursday, April 1, 2021
 
Rocket
 
2

Since you're referencing an id you need to make the foreign key column unsigned. As ids are by default unsigned (non-negative).

So do this for all your foreign keys:

$table->integer('product_id')->unsigned();
Saturday, May 29, 2021
 
IvanH
 
5

The error is due to ->update(['locked', 1]); which should be ->update(['locked' => 1]);

the update function uses an array as "column" => "value", your syntax error causes Laravel to think [ 0 => 'locked', 1 => 1], so it translates to this SQL SET 0 = 'locked', 1 = 1...

Tuesday, August 3, 2021
 
5
->where(DB::raw("(ST_Distance_Sphere(POINT(".$lon.",".$lat."), POINT(lon,lat))/1000)"), '<', 200)

instead of ->havingRaw("(st_distance_sphere( POINT(?, ?) , point(lon, lat))/1000) < ?", [$lon, $lat, $radius])

Friday, August 6, 2021
 
GGio
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share