CODEWITHSUNDEEP
phplaravellaravel-5eloquent
nischalinn
nischalinn

Reputation: 1175

eloquent query for getting difference of two columns from different table

I've to show the remaining quantity of books using eloquent query. I can get total number of purchased books from this table

ordered_books
BOOKCODE[varchar(10)]   QUNATITY[varchar(6)]
111                         25
423                         15
201                         10
111                         10
423                         10
201                         5
158                         12

At first I've to sum the total books for each of the book code. Then from the another table I've to calculate the difference.

books_out
DISTRIBUTOR[varchar(50)]    BOOKCODE[varchar(10)]           QUNATITY[varchar(6)]    
25                              158                             2
35                              201                             5
45                              158                             5
55                              111                             10
35                              111                             5
15                              423                             1
25                              423                             10

Again, from this table I've to calculate the total number of books taken by distributors then I can get the actual number of books present. How shall I write eloquent query for this scenario?

Upvotes: 0

Views: 1481

Answers (2)

user4221591
user4221591

Reputation: 2150

Regarding your scenario, I think you require this query, please try it and comment whether it was helpful or not.

$countBooks = DB::select(DB::Raw(" SELECT A.TOTAL, IFNULL(B.SOLD,0) AS SOLD, (A.TOTAL - IFNULL(B.SOLD,0)) AS REMAINING FROM 
                                                (
                                                    select t1.BookID, SUM(t1.QUNATITY) AS TOTAL from ordered_books t1 
                                                    GROUP BY t1.BookID) A 
                                                LEFT JOIN 
                                                (
                                                    SELECT BookCode, IFNULL(SUM(Quantity),0) AS SOLD FROM books_outs GROUP BY BookCode) B 
                                                ON A.BookID = B.BookCode
                                    "));

dump($countBooks);

Also, better to display the name of books rather than code. It will be more user friendly. Thank You!!!

Upvotes: 0

Jalin
Jalin

Reputation: 95

Try to use Raw query like this
==================================

$data=DB::select(DB::Raw("select t1.BOOKCODE,(total-selled) as available from (select BOOKCODE, SUM(QUNATITY) as total FROM `ordered_books` group by `BOOKCODE`) as t1,(SELECT BOOKCODE,SUM(QUNATITY) as selled FROM `books_out` group by `BOOKCODE`) as t2 where t1.BOOKCODE=t2.`BOOKCODE`"));
        
print_r($data);

//To get total books for each of the book code
SELECT BOOKCODE,SUM(QUNATITY) FROM `ordered_books` group by `BOOKCODE`

//To get total books_out for each of the book code
SELECT BOOKCODE,SUM(QUNATITY) FROM `books_out` group by `BOOKCODE`

Upvotes: 1

Related Questions