Home / Questions / 406319
Accepted
maraet
Asked: 2021-11-13 14:43:29 +0800 CST 2021-11-13 14:43:29 +0800 CST

Increase exposure of a service

  • 772

I am thinking of making a payment system and that through it there are 3 plans (Basic, Featured and super featured). I already have the payment gateways, I designed the logic of the subscription plan and I still need how to highlight these publications.

Basically I have the idea of ​​putting together the following tables:

enter image description here

Now to show the publications I have a search engine and it returns everything found based on what was searched:

$publicaciones = Publication::search($request->get('termino'))->get();

Can you enlighten me how I can show the paid publications first and then the ones that do not have exposure?

sql

1 Answers

  1. Best Answer

    One way to achieve this is ORDER BYby using the following:

    SELECT
      p.nombre AS nombre
FROM publicaciones p
ORDER BY p.publi_id NOT IN (SELECT DISTINCT publi_id FROM suscripciones)

    Basically the ordering criteria will be guided by whether or not the publication is in the subscriptions table, in this case, it will first show those that are.

    With this subqueryyou get the id of the publications that have subscription (paid).

    SELECT DISTINCT publi_id FROM suscripciones

    Here is a working example.

    Since you're using orderByRawEloquent , you can help yourself . You haven't shown how you do the query but here's an example:

    Publicacion::orderByRaw("NOT IN (SELECT DISTINCT publi_id FROM suscripciones)")
     ->get();
    • 1