How to transform this query to lavarel?

Asked

Viewed 38 times

-1

I have a query and I need to turn it into lavarel code.

I have no idea how to do it.

select DISTINCT (
    SELECT distinct
    products.name
    FROM
    products 
    join topics on topics.product_id = products.id ) AS product

from 
contents
join content_topics on content_topics.content_id = contents.id
join topics b on b.id = content_topics.topic_id 
where contents.id = 778;

This content.id will be $request->id

public function listProduct(Request $request){
        if(isset($request->id)){
            $data = ??
        }
    }
php lavarel

  • Gabriel from which table is from, content or products?

  • From the table Contents

  • Have you ever run this query in the bank? it worked?

  • yes, this query works

  • I get it, I’ll edit my answer.

2 answers

0


Access Laravel documentation to learn more about the DB class Query

There are many ways to do this, the simplest is with db select:

$data = DB::select("select DISTINCT (
            SELECT distinct
            products.name
            FROM
            products
            join topics on topics.product_id = products.id ) AS product

        from
        contents
        join content_topics on content_topics.content_id = contents.id
        join topics b on b.id = content_topics.topic_id
        where contents.id = 778")
        ->get();

Normal form:

$data = DB::table('contents')
    ->selectRaw("select DISTINCT (
        SELECT distinct
        products.name
        FROM
        products
        join topics on topics.product_id = products.id ) AS product")
    ->join('content_topics','content_topics.content_id','=','contents.id')
    ->join('topics','topics.id','=','content_topics.topic_id')
    ->where('contents.id',$request->id)
    ->get();

  • Both your two ways me error, the first with get error and the second query error

  • The query it generates works perfectly D; @Glenys Mitchell

  • What mistake it makes?

  • SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'SELECT DISTINCT (SELECT DISTINCT products.name FROM products JOIN Topics ON Topi' at line 1

  • (SQL: select SELECT DISTINCT (SELECT DISTINCT products.name FROM products JOIN Topics ON Topics.product_id = products.id ) AS product from contents Inner Join content_topics on content_topics.content_id = contents.id Inner Join topics on topics.id = content_topics.topic_id Where contents.id = 778)"

  • Both errors are bank =( your query must have some error.

  • No bro, the query works perfectly

  • I understood why, when you put selectRaw, it already plays a "select" in your query, then it was interpreting SQL as select

  • I made a correction on Monday

  • Let’s go continue this discussion in chat.

  • Ahhhhhh true, don’t really need.

  • Look at the chat room my dear

  • Just put the request as put in the chat

Show 8 more comments

0

As Glenys comments there are several ways to query, I will give a hint using Eloquent instead of Facade DB.

$query = Model::query();
$query->where(function($q){
$q->select('*')->distinct()->from('tabela')->get();
});

$query->join('tabel.id','=','outratabel.id');
$query->where('contents.id','778')
$query->paginate(15);

As the friend I advise to look at the Eloquent

Browser other questions tagged php lavarel

You are not signed in. Login or sign up in order to post.