How Can I convert a simple SQL Query to Eloquent

First of all that query is a cartesian product since there is no join between mer_bom_material and new_order. It will run slow and probably not give the desired result.

It should be

SELECT b.pi_no 
FROM mer_bom_material b,
         new_order a
WHERE b.pi_no = a.pi_no
AND a.order_no = :p_order

If that is the table structure. I would suggest using better aliases than a and b. mer_bom_material should be mbm and new_order should be no. I also highly recommend using plural on the table names mer_bom_materials and new_orders

To do this is Eloquent you create a relationship between the models.

Read this link on how to do that https://laravel.com/docs/5.6/eloquent-relationships

You can also push the query down into the database as a view to make the Eloquent more elegant.

Here's how to create a view in mysql https://www.w3schools.com/sql/sql_view.asp

Then you could query it like this

$orderItems = ViewModel::where('order_no', $orderNo)->get();