Question 417528 in Laravel Question posted in
The official Laravel Documentation can be found here.

How Laravel ORM query a string field?

    $orderId = 319769;
    $sql = ShipOrderModel::query()->where('order_id', (string)$orderId);
    // select * from `hr_ship_order` where `order_id` = 319769
    $sql = ShipOrderModel::query()->where('order_id', '319769');
    // select * from `hr_ship_order` where `order_id` = 319769
    $sql = ShipOrderModel::query()->whereRaw('order_id = "?"', $orderId);
    //select * from `hr_ship_order` where order_id = "319769"

The field order_id of ship_order is varchar, i know it’s unbelievable but it’s hard to change the field type. so when i try to query the order id ,i find the type be transfer to integer, which cause the table index no effect. i tried the code like eg, it’s still not work!
help me!plz!!!

Tags:

2

Answers


  1. 0

    You may try like this.

    $orderId = 319769;
$orderId = (string)$orderId;
$sql = ShipOrderModel::query()->where('order_id', '=', $orderId)->get();
    Login or Signup to reply.
  2. 0

    I would try the following:

    First, let’s examine the SQL side.
    Check your table indexes:

    SHOW INDEX FROM hr_ship_order;

    There should be an index on the order_id column.

    Then make sure that your SQL statements are working correctly.

    Use the EXPLAIN command to see if the given SELECT statement is utilizing the table indexes:

    Put the order_id in the SELECT as an integer:

    EXPLAIN SELECT * FROM `hr_ship_order` WHERE `order_id` = 319769;

    Then, as a string:

    EXPLAIN SELECT * FROM `hr_ship_order` WHERE `order_id` = "319769";

    Check the possible_keys and key columns in the result for both cases. In the second case, you should be able to see the indexes used during the SELECT.

    Now, let’s move on to the PHP/Laravel side.

    Let’s see the SQL result of your query:

    dd(ShipOrderModel::where('order_id', $orderId)->toSql());

    You should see something like this:

    "select * from `hr_ship_order` where `order_id` = ?"

    If you need the $orderId as a string everywhere, it might be a good idea to set a cast in the ShipOrderModel.

    If order_id is a regular column:

    protected $casts = [
    'order_id' => 'string'
];

    If order_id is the primary key of your table:

    protected $keyType = 'string';

    Finally, check if the $orderId is still a string in the WHERE clause of the SELECT statement. Let’s dd your query and check:

    dd(ShipOrderModel::where('order_id', $orderId)

    You can verify this by checking the output of the command above. Find the wheres array:

    wheres: array:1 [▼
  0 => array:5 [▼
    "type" => "Basic"
    "column" => "order_id"
    "operator" => "="
    "value" => "319769"
    "boolean" => "and"
  ]
]

    If the "value" is still a string, then everything is fine.

    Last question, which could have been the first. 🙂
    What PHP and Laravel versions are you using?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search