Need help with table relationships.

Hi everyone I am working on a customer text messaging app for a group of small businesses. I have the following tables.

Users, Stores, Customers, Messages and pivot table Store_User.

User and Stores table are linked using the pivot table. Customer table has store_id foreign key. Messages table has customer_id foreign key.

What I would like to do is pull all messages for customers of stores that the user has access to do.

I did get it somewhat working by using nested loops.

@foreach ($user->stores as $store)
	@foreach ($store->customers as $customer)
		@foreach ($customer->messages as $messages)
			{{ $message->text }}
		@endforeach
	@endforeach
@endforeach

This kind of works but the messages do not show from latest to oldest. They get grouped for each customer and then sorted latest to oldest.

For example: if I had a told of 9 messages sent to random 3 customers.

Customer 1 Message 1 Message 3 Message 4 Customer 2 Message 2 Message 6 Message 9 Customer 3 Message 5 Message 7 Message 8

What I would like to look like is:

Message 1 Message 2 Message 3 Message 4 Message 5 Message 6 Message 7 Message 8 Message 9

If someone could please tell me a way to accomplish this without creating another pivot table.

TIA.

bobbybouwmann

6 years ago

Best Answer

Level 88

There are different ways of solving this, but it also depends on how you want to display it.

You can retrieve all the data starting from messages instead of the user. Something like this

Message::with('customer.store.user')
    ->whereHas('customer.store.user' => function ($query)  {
        return $query->where('users.id', auth()->user()->id;
    })->latest()->get();

Let me know if that is an option for you.

tmomin

6 years ago

Level 1

Thank you so much, I knew I had to start from messages but couldn't out the whole query.

Please or to participate in this conversation.