如何在Laravel查询构建器中编写子查询

Question

i have an mssql query that looks like this:

SELECT * FROM (

    SELECT a.Client, ad.Kto, a.Address, a.Matchcode, a.Name1, a.country, a.ZIP, a.Street FROM [HeadQuarter].[dbo].[Addresses] a
            INNER JOIN [HeadQuarter].[dbo].[AddressesDetails] ad ON (a.Client = ad.Client AND 
                                                                 a.Address = ad.Address)

    WHERE ad.Active <> 0 AND
          (a.USER_emailactive = 0 OR a.USER_emailactive IS NULL)

) client_id

WHERE (a.Country = 'AT' AND a.ZIP BETWEEN '0000' AND '5000')

i converted the inner select into Laravel query builder

\DB::connection('sqlsrv')
->table('[HeadQuarter].[dbo].[Addresses]')
->join([HeadQuarter].[dbo].[AddressesDetails], function($join){

$join->on('[HeadQuarter].[dbo].[Addresses].Client', '=', '[HeadQuarter].[dbo].[AddressesDetails].Client')
     ->on('[HeadQuarter].[dbo].[Addresses].Address', '=', '[HeadQuarter].[dbo].[AddressesDetails].Address')

})
->select('[HeadQuarter].[dbo].[Addresses].Client, [HeadQuarter].[dbo].[AddressesDetails].Kto,....')
->where('[HeadQuarter].[dbo].[AddressesDetails].Active', '<>', '0')
->whereRaw('(a.USER_emailactive = 0 OR a.USER_emailactive IS NULL)')
->get();

and this is working. But now how can i get the

SELECT * FROM (..inner query..) client_id 
WHERE (a.Country = 'AT' AND a.ZIP BETWEEN '0000' AND '5000')

convert to my query builder. sure i could use ->select() and write the raw sql query but i need this in the query builder because my inner and outer where clause i optional

Answer 1

I guess you could simplify your query as below, there is not need for sub query

SELECT a.Client, ad.Kto, a.Address, a.Matchcode, a.Name1, a.country, a.ZIP, a.Street 
FROM [HeadQuarter].[dbo].[Addresses] a
INNER JOIN [HeadQuarter].[dbo].[AddressesDetails] ad 
ON (a.Client = ad.Client AND a.Address = ad.Address)
WHERE ad.Active <> 0 
    AND a.Country = 'AT' 
    AND a.ZIP BETWEEN '0000' AND '5000'
    AND (a.USER_emailactive = 0 OR a.USER_emailactive IS NULL)

In query builder you can use Parameter Grouping

\DB::connection('sqlsrv')
    ->table('[HeadQuarter].[dbo].[Addresses] as a')
    ->join('[HeadQuarter].[dbo].[AddressesDetails] as b', function($join){
        $join->on('a.Client', '=', 'ad.Client')
             ->on('a.Address', '=', 'ad.Address');

    })
    ->select('a.Client', 'ad.Kto', 'a.Address', 'a.Matchcode', 'a.Name1', 'a.country', 'a.ZIP', 'a.Street')
    ->where('ad.Active', '<>', '0')
    ->where(function ($query) {
        $query->whereNull('a.USER_emailactive')
              ->orWhere('a.USER_emailactive', '=', '0');
     })
     ->where(function ($query) {
        $query->orWhere(function ($query) {
            $query->where('a.Country', '<>', 'AT')
                  ->whereBetween('a.ZIP', ['0000', '5000']);
        })->orWhere(function ($query) {
            $query->where('a.Country', '<>', 'Foo')
                  ->whereBetween('a.ZIP', ['0000', '5000']);
        })->orWhere(function ($query) {
            $query->where('a.Country', '<>', 'Bar')
                  ->whereBetween('a.ZIP', ['0000', '5000']);
        });
    })
    ->get();