How to execute AR Query in Yii to select records by value of related data?

Question

PostgreSQL

Good afternoon,
There are 2 tables:
m_bids
------------
id
mc_points
------------
id
bid_id
time_start(int)
time_finish(int)
is_tart(bool)
For one entries in m_bids can only be 2 entries in mc_points . The first entry in mc_points is the starting point ( is_start(true) ) , the second point is the finish.
time_start and time_finish are defined for the start point - start range.
We need to get all m_bids whose time_finish at the start point is greater than the current time (we don't care about the end point, because time_startand time_finish are empty). Something like :

SELECT * FROM m_bids LEFT JOIN mc_points ON m_bids.id  = mc_points.bid_id WHERE mc_pointsюtime_finish  is not NULL AND mc_points.time_finish  < 'x';

In the links for Bids , it is set:
'points' => [self::HAS_MANY, 'Points', 'bid_id'],
But I don’t need to do the check in the cycle, but the AR
request. I started with this:

$criteria = new CDbCriteria();
$criteria->join = 'LEFT JOIN mc_points ON `m_bids`.`id` = mc_points.bid_id';
$criteria->condition = 'mc_points time_finish IS NOT NULL AND time_finish  < '.time();
$model = Bids::model()->published()->getAll($criteria);

Catching :
CDbCommand failed to execute SQL query: SQLSTATE[42P01]: Undefined table: 7 ERROR: invalid reference to FROM-clause entry for table "m_bids"
LINE 1: ..._car_id" FROM "m_bids" "t" LEFT JOIN mc_points ON m_bids.id ...
^
HINT: Perhaps you meant to reference the table alias "t".. The SQL statement executed was: SELECT "t"."id", "t"."customer_id", "t"."driver_id", "t". "wish_price", "t"."views", "t"."name", "t"."name_kz", "t"."name_en", "t"."name_alias", "t"."description ", "t"."description_en", "t"."description_kz", "t"."meta_d", "t"."meta_k", "t"."order_sort", "t". "t"."sys_date_update", "t"."sys_user", "t"."exist", "t"."urgency", "t"."shifr", "t"."name_full", "t"."name_full_kz", "t"."price_id", "t"."photo", "t"."status_id", "t"."category_car_id " FROM "m_bids" "t" LEFT JOIN mc_points ON m_bids.id = mc_points.bid_id WHERE (exist = 1) AND (mc_points.time_finish IS NOT NULL AND time_finish < 1441078315)
m_bids - 100% goes by the table for class Bids , which I am I doing it wrong?id = mc_points.bid_id WHERE (exist = 1) AND (mc_points.time_finish IS NOT NULL AND time_finish < 1441078315) m_bids - 100% goes to table for class Bids , what am I doing wrong?id = mc_points.bid_id WHERE (exist = 1) AND (mc_points.time_finish IS NOT NULL AND time_finish < 1441078315) m_bids - 100% goes to table for class Bids , what am I doing wrong?

Answer 1

SELECT * FROM m_bids LEFT JOIN mc_points ON m_bids.id = mc_points.bid_id WHERE mc_points.time_finish is not NULL AND mc_points.time_finish > CURDATE();