Laravel nova custom indexQuery

I have a tickets table and a customer's table. There is a relationship with the customer's table in that a customer has many tickets. On the tickets index page, I would like to display only the latest ticket for each customer. I understand I can override the indexQuery method of a nova resource. This is a raw SQL query that helps me achieve that

$newquery="WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id DESC) AS rn FROM tickets AS m WHERE m.entity_id = 1 ) SELECT * FROM ranked_messages WHERE rn = 1;";

How can i use this custom query in the indexQuery?

cherlow

4 years ago

Best Answer

Level 1

found the solution. I made a subquery that ranks the tickets and used laravel fromSub to return the raw with rank 1

		$rawquery = " *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id DESC) AS rn ";

        $subquery = TicketModel::selectRaw($rawquery);

        return $query->fromSub($subquery, "tickets")->where("rn", 1)->get(); ```

2 likes

Please or to participate in this conversation.