活动记录的where语句 - active recored where statement

active recored where statement

本文关键字：语句 where 记录活动 | 更新日期: 2023-09-27

在codeigniter中，我试图生成以下SQL语句:

SELECT * 
FROM (`ea_users`,`ea_appointments`,`ea_services`) 
WHERE `ea_appointments`.`id_users_customer` = `ea_users`.`id` 
AND `ea_appointments`.`id_services` = `ea_services`.`id` 
AND `ea_appointments`.`start_datetime`> '2015-07-18 00:00:00'
AND `ea_appointments`.`start_datetime`< '2015-07-18 23:59:59'

在活动记录格式中，我已经尝试过了:

$day_start = date('Y-m-d 00:00:00', $day);
$day_end = date('Y-m-d 23:59:59', $day);
return $this->db->select('*')
  ->from('ea_appointments,ea_services,ea_users')
  ->where('ea_appointments.id_users_customer','ea_users.id') 
  ->where('ea_appointments.id_services','ea_services.id')
  ->where('ea_appointments.start_datetime>',$day_start)
  ->where('ea_appointments.start_datetime<',$day_end)
  ->get()->result();

但是它产生了这个:

SELECT *
FROM (`ea_appointments`, `ea_services`, `ea_users`)
WHERE `ea_appointments`.`id_users_customer` =  'ea_users.id'
AND `ea_appointments`.`id_services` =  'ea_services.id'
AND `ea_appointments`.`start_datetime>` '2015-07-18 00:00:00'
AND `ea_appointments`.`start_datetime<` '2015-07-18 23:59:59'

如何获得ea_users。Id '和'ea_services. Id '。Id '被翻译为'ea_users'。'id'和'ea_services'.'id'?我试过了:

  ->where('ea_appointments.id_users_customer','ea_users'.'id') 
  ->where('ea_appointments.id_services','ea_services'.'id')

但是这产生了这个

WHERE `ea_appointments`.`id_users_customer` =  'ea_usersid'
AND `ea_appointments`.`id_services` =  'ea_servicesid'

正确的格式是什么?

这应该可以工作:(> <迹象)>

$day_start = date('Y-m-d 00:00:00', $day);
$day_end = date('Y-m-d 23:59:59', $day);
return $this->db->select('*')
->from('ea_appointments,ea_services,ea_users')
->where('ea_appointments.id_users_customer','ea_users.id') 
->where('ea_appointments.id_services','ea_services.id')
->where('ea_appointments.start_datetime >',$day_start)
->where('ea_appointments.start_datetime <',$day_end)
->get()->result();

假设您的原始sql函数在您尝试在codeigniter中重新创建它之前像您预期的那样工作，您可以简单地这样做。

$day_start = date('Y-m-d 00:00:00', $day);
$day_end = date('Y-m-d 23:59:59', $day);
$query = $this->db->query(SELECT * 
FROM (ea_users, ea_appointments, ea_services) 
WHERE ea_appointments.id_users_customer = ea_users.id 
AND ea_appointments.id_services = ea_services.id 
AND ea_appointments.start_datetime > {$day_start}
AND ea_appointments.start_datetime < {$day_end});
$query->result();

成功了:

$day_start = date('Y-m-d 00:00:00', $day);
$day_end = date('Y-m-d 23:59:59', $day);
return $this->db->select('ea_appointments.id,
 ea_appointments.start_datetime,
 ea_appointments.end_datetime, 
 ea_users.email, 
 ea_services.name')
  ->from('ea_appointments')
  ->join('ea_users', 'ea_appointments.id_users_customer = ea_users.id','left') 
  ->join('ea_services', 'ea_appointments.id_services = ea_services.id','left')
  ->where('ea_appointments.start_datetime >',$day_start)
  ->where('ea_appointments.start_datetime <',$day_end)
  ->get()->result();