Laravel по-русски

polishchuk_i

Сообщений: 2

Запрос не в RAW

Добрый вечер.
Есть такой запрос в MySql
Пытаюсь перетянуть запросы к сайту каталога запчастей на Tecdoc в Laravel/
Столкнулся с тем, что данный запрос не отрабатывается.
Таблицы ART_LOOKUP2 нету, есть перелинковка из ART_LOOKUP.
Если есть мысли, поделитесь!

SELECT DISTINCT
IF (ART_LOOKUP2.ARL_KIND = 3, BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND) AS BRAND,
IF (ART_LOOKUP2.ARL_KIND IN (2, 3), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR) AS NUMBER,
ART_LOOKUP2.ARL_KIND
FROM          ART_LOOKUP
LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID
INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID
INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID
INNER JOIN ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY) ON ART_LOOKUP2.ARL_ART_ID = ART_LOOKUP.ARL_ART_ID
LEFT JOIN BRANDS AS BRANDS2 ON BRANDS2.BRA_ID = ART_LOOKUP2.ARL_BRA_ID
INNER JOIN ARTICLES AS ARTICLES2 ON ARTICLES2.ART_ID = ART_LOOKUP2.ARL_ART_ID
INNER JOIN SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY) ON SUPPLIERS2.SUP_ID = ARTICLES2.ART_SUP_ID
WHERE
ART_LOOKUP.ARL_SEARCH_NUMBER = @NUMBER AND
(ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = @BRAND OR  SUPPLIERS.SUP_BRAND = @BRAND) AND
(ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND) IN	((1, 1), (1, 2), (1, 3),(2, 1), (2, 2), (2, 3),(3, 1), (3, 2), (3, 3), (4, 1))
ORDER BY BRAND, NUMBER;

В navicat запрос отдает ответ.....
В ORM не могу довести до ума((((

Запрос получился такой

$Numbers = DB::table('ART_LOOKUP')
            ->leftjoin('BRANDS','BRANDS.BRA_ID','=','ART_LOOKUP.ARL_BRA_ID')
            ->join('ARTICLES','ARTICLES.ART_ID','=','ART_LOOKUP.ARL_ART_ID')
            ->join('SUPPLIERS','SUPPLIERS.SUP_ID','=','ARTICLES.ART_SUP_ID')
            ->join('ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY)','ART_LOOKUP2.ARL_ART_ID','=','ART_LOOKUP.ARL_ART_ID')
            ->leftjoin('BRANDS AS BRANDS2','BRANDS2.BRA_ID','=','ART_LOOKUP2.ARL_BRA_ID')
            ->join('ARTICLES AS ARTICLES2','ARTICLES2.ART_ID','=','ART_LOOKUP2.ARL_ART_ID')
            ->join('SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY)','SUPPLIERS2.SUP_ID','=','ARTICLES2.ART_SUP_ID')

            
            ->select(DB::raw('IF ( ART_LOOKUP2.ARL_KIND = "3", BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND ) AS BRAND'),
                    DB::raw('IF ( ART_LOOKUP2.ARL_KIND IN ( "2", "3"), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR ) AS ARTICLE'),
                    'ART_LOOKUP2.ARL_KIND AS KIND')
            

            ->whereIn('ART_LOOKUP.ARL_KIND', ( ["3", "4"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["1", "1"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["1", "2"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["1", "3"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["2", "1"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["2", "2"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["2", "3"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["3", "1"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["3", "2"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["3", "3"]))
            ->whereIn('ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND', (["4", "1"]))
            
            
            ->where('ART_LOOKUP.ARL_SEARCH_NUMBER', '=', $Number)
            ->where('BRANDS.BRA_BRAND', '=', $Brand)
            ->where('SUPPLIERS.SUP_BRAND', '=', $Brand)
            
            ->orderBy('KIND', 'DESC')
            ->orderBy('BRAND', 'ASC')
            ->orderBy('ARTICLE', 'ASC')
            ->distinct()->get();

Ошибка звучит:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ART_LOOKUP2.ARL_KIND' in 'field list' (SQL: select distinct IF ( ART_LOOKUP2.ARL_KIND = "3", BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND ) AS BRAND, IF ( ART_LOOKUP2.ARL_KIND IN ( "2", "3"), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR ) AS ARTICLE, `ART_LOOKUP2`.`ARL_KIND` as `KIND` from `ART_LOOKUP` left join `BRANDS` on `BRANDS`.`BRA_ID` = `ART_LOOKUP`.`ARL_BRA_ID` inner join `ARTICLES` on `ARTICLES`.`ART_ID` = `ART_LOOKUP`.`ARL_ART_ID` inner join `SUPPLIERS` on `SUPPLIERS`.`SUP_ID` = `ARTICLES`.`ART_SUP_ID` inner join `ART_LOOKUP` as `ART_LOOKUP2 FORCE KEY (PRIMARY)` on `ART_LOOKUP2`.`ARL_ART_ID` = `ART_LOOKUP`.`ARL_ART_ID` left join `BRANDS` as `BRANDS2` on `BRANDS2`.`BRA_ID` = `ART_LOOKUP2`.`ARL_BRA_ID` inner join `ARTICLES` as `ARTICLES2` on `ARTICLES2`.`ART_ID` = `ART_LOOKUP2`.`ARL_ART_ID` inner join `SUPPLIERS` as `SUPPLIERS2 FORCE KEY (PRIMARY)` on `SUPPLIERS2`.`SUP_ID` = `ARTICLES2`.`ART_SUP_ID` where `ART_LOOKUP`.`ARL_SEARCH_NUMBER` = 0451103336 and `BRANDS`.`BRA_BRAND` = bosch and `SUPPLIERS`.`SUP_BRAND` = bosch)

Изменено polishchuk_i (28.12.2019 21:30:15)

nailfor +21

Сообщений: 40

Статей: 2

Re: Запрос не в RAW

inner join `ART_LOOKUP` as `ART_LOOKUP2 FORCE KEY (PRIMARY)` on `ART_LOOKUP2`.`ARL_ART_ID` = `ART_LOOK.`ARL_ART_ID` 

думаю причина в этом

polishchuk_i

Сообщений: 2

Re: Запрос не в RAW

Переписал в raw - работает.
Тему можно закрывать!