16 декабря 2013 в 17:29
Добрый день.
Прошу помощи по решению следующей задачи.
Есть нода типа Договор.
Есть нода типа Заявка.
У заявки есть поля Сумма и Статус
Нужно выбрать все договоры у заявок которого статус равен оплачено и при этом-же выводить договоры у которых совсем нет заявок.
Все это нужно сделать Views
Задача решена частично:
1 вариант - выводит договоры у которых есть заявки со статусом оплачено.
2 вариант - выводит договоры у которых нет заявок и так-же выводит договоры у заявок которого имеется статус оплачено. Но при этом не выводит договоры у которых есть только одна заявка в отличном от оплачено статусе.
3 вариант статус отфильтровать удалось в соединении но это ничего не дало т.к. сумма содержится совсем в другой таблице и она присоеденяется к результату и участвует в вычислениях что мне не нужно.
В обычном SQL я бы решил эту задачу через вложенный запрос. Не селен в API Views. Подскажите как можно решить данную задачу.
Комментарии
А должно выводить?
Не оч понятно. У 1го договора может быть много заявок? Нужно выводить договры у которых все заявки в статусе оплачено или хотя бы одна?
Ну и откажитесь от вьюс. Проще будет.
Бомбите сюда свой запрос, думаю его можно переписать с джойнами.
В заявке есть поле ссылка Entity Reference на договор. У договора может быть много заявок. Должны выводится все заявки со статусом оплачено. И все договоры да-же если у них нет заявок.
Если просто добавить фильтры и отношения то да-же если не ставить галку "связь обязательна" в отношениях договоры у которых нет заявок пропадут по фильтру статуса. Через хук изменил фильтр по статусу с INNER на LEFT. Договоры без заявок остаются, но пропадают когда есть одна заявка не в статусе "оплачено". Получается это из-за JOIN-а. Заявка то есть вот потом эта строка и отфильтровывается через условие по статусу. Статус получается и не NULL и не Оплачено.
Через хук изменил фильтр по статусу и добавил условие в объединение (ON в JOIN) что-бы не объединялись записи не в статусе "оплачено". Но тут появилась другая проблема - другие поля естественно нормально так присоеденяются и в результирующей таблице присутствуют поля для заявки которую нужно отфильтровать.
Подзапрос на мой взгляд нужен что-бы в нем уже отобрать все заявки в нужном статусе, а уже потом объединить с договорами.
от Views не хотелось бы отказываться, да и разобраться с ними хотел бы.
Вот запрос. Из него повыкидывал лишние Join-ы. В этом запросе уже добавил в ON Join-а по статусу условие fdfr_status.field_request_status_tid = '37' но это не решило проблему, если бы все остальные значения были бы в одной таблице то они не попали бы в выборку а так все попадает, а нужно что-бы не попадало.
FROM
dr_node node
LEFT JOIN dr_field_data_field_request_treaty field_data_field_request_treaty ON node.nid = field_data_field_request_treaty.field_request_treaty_target_id
LEFT JOIN dr_node field_request_treaty_node ON field_data_field_request_treaty.entity_id = field_request_treaty_node.nid
LEFT JOIN dr_field_data_field_request_status fdfr_status ON field_request_treaty_node.nid = fdfr_status.entity_id AND (fdfr_status.entity_type = 'node' AND fdfr_status.deleted = '0' AND fdfr_status.field_request_status_tid = '37')
LEFT JOIN dr_field_data_field_sum field_request_treaty_node__field_data_field_sum ON field_request_treaty_node.nid = field_request_treaty_node__field_data_field_sum.entity_id AND (field_request_treaty_node__field_data_field_sum.entity_type = 'node' AND field_request_treaty_node__field_data_field_sum.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('treaty')) ))
Так должны выводиться договоры или заявки?
Не корректно наверное выразился. Выводится должны все договоры, но по договору выводится сумма заявок которое считается из поля суммы в заявке. То есть мы выводим договоры, а сумму считаем по всем заявкам со статусом оплачено. Сами по себе заявки не выводятся а только участвуют в отборах и подсчете суммы.
Вам надо вывести все договоры, потом добавить отношение к заявкам, по этому отношению вывести все заявки, сгруппировать по договору, в вашем случае достаточно тока поле сумма(я бы для проверки вывел все тайтлы заявок, потом же их все равно можно удалить), а потом агрегировать результат по полю сумма. примерно так. даже итнтересно стало сработает или нет. отпишитесь.
Теперь понятно. Вам нужно что-то вроде:
contracts.nid as contract_id,
count(bid_status.bid_id) as paid_bids, /* число оплаченных заявок */
sum(bid_amount.amount) as paid_bids_total /* сумма оплаченных заявок */
from node as contracts
left join contract_bids on contract_bids.contract_id = contracts.nid
left join node as bids on bids.nid = contract_bids /* заявки на договор */
left join bid_status on bid_status.bid_id = bids.nid and bid_status.status = 'active' /* присоединим оплаченные заявки */
left join bid_amount on bid_amount.bid_id = bid_status.bid_id
where node.type = 'treaty'
group by contracts.nid
Тут человекочитаемые названия таблиц и полей, вам нужно просто заменить их на друпаловскую белиберду типа dr_field_data_field_request_treaty
Насколько я помню, views 3 поддерживают аггрегационные функции, так что такой запрос можно составить без кодинга.
И насколько я помню
это как раз меняет inner join на left join, так что ваш хук тоже можно выкинуть.
Это меняет INNER на LEFT в отношениях, но не в фильтрах. Для строки:
LEFT JOIN node AS bids ON bids.nid = contract_bids /* заявки на договор */А мне нужно было для строки
LEFT JOIN bid_status ON bid_status.bid_id = bids.nid AND bid_status.status = 'active'Если просто добавить фильтр по статусу до добавится условие в where, что-то типа
А строка с присоединением статуса будет
Все это приведет к тому что не будут выводится договоры у которых нет заявок. И без хуков не обойтись.
Запрос который вы написали работать не будет так как надо (такой вариант я то-же проходил). В результате мы значение статуса не получим, а вот сумму paid_bids_total он посчитает. Это при условии что будет одна заявка со статусом не 'active'. Если будет хоть одна заявка со статусом 'active' то будет все ок.
Если брать за основу ваш запрос, сейчас он имеет такой вид
contracts.nid AS contract_id,
COUNT(bid_status.bid_id) AS paid_bids, /* число оплаченных заявок */
SUM(bid_amount.amount) AS paid_bids_total /* сумма оплаченных заявок */
FROM node AS contracts
LEFT JOIN contract_bids ON contract_bids.contract_id = contracts.nid
LEFT JOIN node AS bids ON bids.nid = contract_bids /* заявки на договор */
LEFT JOIN bid_status ON bid_status.bid_id = bids.nid /* присоединим оплаченные заявки */
LEFT JOIN bid_amount ON bid_amount.bid_id = bid_status.bid_id
WHERE node.type = 'treaty' AND (bid_status.status = 'active' OR bid_status.status IS NULL)
GROUP BY contracts.nid
Но как я писал при этом теряются договора у которых есть одна или более заявки в статусе отличном от 'active'
Вот запрос с подзапросом который решает задачу, как его построить средствами Views пусть и с кодингом я не представляю.
FROM dr_node node
LEFT JOIN (
/* Получаем нужные нам заявки */
SELECT fdfr_treaty.field_request_treaty_target_id, node_request.nid, fdf_sum.field_sum_value, fdfr_status.field_request_status_tid
FROM dr_node node_request
LEFT JOIN dr_field_data_field_request_treaty fdfr_treaty ON node_request.nid = fdfr_treaty.entity_id AND (fdfr_treaty.entity_type = 'node' AND fdfr_treaty.deleted = '0')
LEFT JOIN dr_field_data_field_request_status fdfr_status ON node_request.nid = fdfr_status.entity_id AND (fdfr_status.entity_type = 'node' AND fdfr_status.deleted = '0')
LEFT JOIN dr_field_data_field_sum fdf_sum ON node_request.nid = fdf_sum.entity_id AND (fdf_sum.entity_type = 'node' AND fdf_sum.deleted = '0')
WHERE fdfr_status.field_request_status_tid = 37 /*Фильтр по статусу и возможные другие отборы по заявкам*/
)request ON node.nid = request.field_request_treaty_target_id
WHERE node.status = '1' AND node.type IN ('treaty')
Если бы данные хранились в одной таблице то достаточно было бы Join-а с условием в ON тогда бы отфильтровалась вся строка таблицы, но у нас данные полей хранятся в разных таблицах и если я отфильтрую поле статуса, то на поле суммы это ни как не повлияет.
Я ниче не понял. Я вам написал запрос, который делает то, что вы сказали:
Вы просто замените названия таблиц на друпаловские и посмотрите, что выйдет.
К слову, запросы
contracts.nid AS contract_id,
COUNT(bid_status.bid_id) AS paid_bids, /* число оплаченных заявок */
SUM(bid_amount.amount) AS paid_bids_total /* сумма оплаченных заявок */
FROM node AS contracts
LEFT JOIN contract_bids ON contract_bids.contract_id = contracts.nid
LEFT JOIN node AS bids ON bids.nid = contract_bids /* заявки на договор */
LEFT JOIN bid_status ON bid_status.bid_id = bids.nid /* присоединим оплаченные заявки */
LEFT JOIN bid_amount ON bid_amount.bid_id = bid_status.bid_id
WHERE node.TYPE = 'treaty' AND (bid_status.STATUS = 'active' OR bid_status.STATUS IS NULL)
GROUP BY contracts.nid
и
contracts.nid AS contract_id,
COUNT(bid_status.bid_id) AS paid_bids, /* число оплаченных заявок */
SUM(bid_amount.amount) AS paid_bids_total /* сумма оплаченных заявок */
FROM node AS contracts
LEFT JOIN contract_bids ON contract_bids.contract_id = contracts.nid
LEFT JOIN node AS bids ON bids.nid = contract_bids.bid_id /* заявки на договор */
LEFT JOIN bid_status ON bid_status.bid_id = bids.nid AND bid_status.status = 'active' /* присоединим оплаченные заявки */
LEFT JOIN bid_amount ON bid_amount.bid_id = bid_status.bid_id
WHERE node.type = 'treaty'
GROUP BY contracts.nid
разные, т.е. означают и возвращают разные вещи. 2й запрос правильный.
А зачем его получать?
Я конечно, еще попробую. Но вот этот запрос
contracts.nid AS contract_id,
COUNT(bid_status.bid_id) AS paid_bids, /* число оплаченных заявок */
SUM(bid_amount.amount) AS paid_bids_total /* сумма оплаченных заявок */
FROM node AS contracts
LEFT JOIN contract_bids ON contract_bids.contract_id = contracts.nid
LEFT JOIN node AS bids ON bids.nid = contract_bids.bid_id /* заявки на договор */
LEFT JOIN bid_status ON bid_status.bid_id = bids.nid AND bid_status.STATUS = 'active' /* присоединим оплаченные заявки */
LEFT JOIN bid_amount ON bid_amount.bid_id = bid_status.bid_id
WHERE node.TYPE = 'treaty'
GROUP BY contracts.nid
не отфильтрует заявки, он уберет только статус в заявках, а сама запись заявки и как следствие все остальные поля останутся. То есть если будет одна или более заявок со статусом не 'active' то сумма все равно посчитается. Хотя не должна.
Этот джойн добавляет поле с ценой.
LEFT JOIN bid_amount ON bid_amount.bid_id = bid_status.bid_idДжойн идёт по полю bid_status.bid_id, если это поле null (как это будет в случае неоплаченной заявки), то и в bid_amount.amount будет null
и в сумму оно не войдет.
Спасибо за разъяснение, попробую и отпишусь потом.