Duplicated records with query

left join - customer has more than one order?

Some customers has more then one order (like customer 8). And as I look at it, it looks that my Laravel in some way uses the customer id as order id, something I don't understand.. For a look at it here is the part that I use to show the data;

<table class="table table-hover" id="items">
    <thead>
        <tr>
            <th>Bestelnummer</td>
            <th>Naam / bedrijfsnaam</th>
            <th>Telefoonnummer(s)</th>
            <th>Besteld op</th>
            <th>Status</th>
            <th>Acties</th>
        </tr>
    </thead>

    <tbody class="orderRules">
        @foreach($orders as $order)
            <tr>
                <td><a href="{{ route('order.edit', ['id' => $order->id]) }}">#{{ $order->id }}</a></td>
                <td>{{ $order->customers->name }}</td>
                <td>0{{ $order->customers->primaryPhoneNumber }} @php echo (empty($order->customers->secondaryPhoneNumber)) ? '' : '/ 0'. $order->customers->secondaryPhoneNumber @endphp</td>
                <td>{{ date("d-m-Y", strtotime($order->created_at)) }}</td>
                <td>{{ Config::get('settings.deliveryStatus')[$order->orderStatus] }}</td>
                <td>
                    <input type="hidden" value="{{ $order->id }}" class="orderId">
                    <a href="{{ route('order.edit', ['id' => $order->id]) }}" class="btn btn-green glyphicon glyphicon-pencil"></a>
                    <button type="button" id="removeOrderWarning" class="btn btn-green" style="margin-left: 10px;" data-orderId="{{ $order->id }}"><span class="glyphicon glyphicon-trash"></span></button>
                </td>
            </tr>
        @endforeach
    </tbody>
</table>

{{ $orders->appends(['search' => Input::get('search')])->links() }}

Laravel does not control your query. The SQL it generates does. You have 2 LIKE's and a OR. This is a very open WHERE clause. No one knows your data but you.

What is the value of $searchQuery?

$searchQuery can be a name of the customer like 'Dan' or a order id like '1'. It can also be empty.

is there any reason why you chose join rather than use eloquent relationship?

one issue you have is that you join two tables that both contain an id column. It looks like customers is the winner since id always equals customer_id so in this query you are losing the order id

@Snapey Yesterday I switched over to eloquent relationships, and now I'm stuck on my query, I have the following:

public function indexnew() {
        Log::info("Called indexnew");

        //Log::debug(Request::get('search'));

        if(isset($_GET['search'])) {
            $searchQuery = $_GET['search'];
        } else {
            $searchQuery = null;
        }

        $orders = Order::whereHas('customers', function($query) use ($searchQuery) {
            $query->where('name', 'LIKE', '%'. $searchQuery .'%');
        })->where('id', 'LIKE', '%'. $searchQuery .'%')->with('customers')->simplePaginate(5);


        Log::debug($orders);
        return view('order.test')->withOrders($orders);
    }

I get a input from $searchquery, and searching on ID works. But when I search for a name like 'Derk' my query fails. I get nothing back, no errors and no result. I tried to use customers.name inside my whereHas but this didnt resolve my issue.

when you search by name, do you get your Log entry Called index new

@snapey I'm getting that. I indeed used it as a way to debug my code. When I log whats comming out of the $orders variable, I receive a array with empty values.

I get nothing back, no errors and no result.

so what you actually mean is not a white screen, you do get the view but the result set is empty. This is critical information.

So you don't have any entries with 'Derk' in the name field?

@snapey Indeed, I'm getting a view, but my results are empty. I have some entries in de name field with the name Derk (like Derk J. and Derk V), but if I search by a order id, I get results.

I think the issue is that for it to return a name match it has to ALSO match the order id since you have a where statement on the outer collection. This is effectively a hard filter. You might match the name, but since the order id is not also 'Derk' then the results get filtered out.

You want a list of orders where the customer is named as per the search term, or the order ID matches.

Personally, I would test the search term. If it evaluates to an integer then just search the order ID, if not then search the customer name (or vice versa)