whereNotBetween给我错误 [英] whereNotBetween giving me error

问题描述

我正在尝试编写whereNotBetween查询，但我不知道我的错误在哪里.它没有给我结果.我知道在whereNotBetween行附近书写时出现错误.没有其他示例.

I am trying to write whereNotBetween query but I dont where is my mistake. its not giving me result. I know something is mistake where i am writing near the line whereNotBetween. There is not any other example.

$values = DB::table('exchanges')
            ->leftJoin('trades', 'exchanges.id', '=', 'trades.exchange_id')
            ->select('trades.*')
            ->where('trades.user_id', $user)

           ->whereRaw('TIME(trades.tradedate) NOT BETWEEN exchanges.start_time AND exchanges.close_time')


            ->get();

'where子句'中的未知列'trade_date'(SQL:从exchanges选择TIME(trades.tradedate)AS trade_date在exchanges上左连接trades.id = trades.exchange_id其中trades.user_id = 1和trade_date不在exchanges.start_time和exchanges.start_time之间)

Unknown column 'trade_date' in 'where clause' (SQL: select TIME(trades.tradedate) AS trade_date from exchanges left join trades on exchanges.id = trades.exchange_id where trades.user_id = 1 and trade_date not between exchanges.start_time and exchanges.start_time)

如果tradedate> $ start_time和$ tradedate<我想得到结果$ close_time然后我想要结果.

I want get result if tradedate > $start_time AND $tradedate < $close_time then i want result.

这是在范围之间..但它给了我结果:

this is between range.. yet its giving me result:

 #items: array:69 [▼
0 => {#1106 ▼
  +"id": 3
  +"exchange": "NSE"
  +"created_at": "2018-04-18 13:00:23"
  +"updated_at": "2018-08-14 06:48:24"
  +"deleted_at": null
  +"start_time": "09:15:00"
  +"close_time": "03:30:00"
  +"country_id": null
  +"user_id": 1
  +"symbol_id": 7
  +"exchange_id": 1
  +"market_id": 1
  +"is_action": 1
  +"rate": 13234
  +"tradedate": "2018-06-21 09:20:00"
  +"note": "Kinnari updated"
  +"quantities": 456
  +"stoploss": 6465

推荐答案

您必须使用Raw查询，因为您使用的是别名，trade_date列不存在，所以使用的是Raw别名. .，因此您必须直接使用子查询，例如:whereRaw('NOT (TIME(trades.tradedate) BETWEEN exchanges.start_time AND exchanges.end_time)')

You gotta use a Raw query, the one you are using wont work because you are using an alias, the trade_date column does not exist, is an alias... so you'll have to use the subquery directly like: whereRaw('NOT (TIME(trades.tradedate) BETWEEN exchanges.start_time AND exchanges.end_time)')

我假设您有一个end_time列...您必须为...之间定义一个范围...如果没有...，直接使用IS EQUAL或=运算符...

I assume u have an end_time column... you gotta define a range... for Between... if not, just use directly the IS EQUAL or = operators...

**编辑2 **

您的范围对于BETWEEN比较无效.

Your ranges are not valid for a BETWEEN comparison.

如果交换在一天的09:00 AM开始并在第二天的03:00 AM结束

If the exchange starts at 09:00 AM of one day and ends on 03:00 AM of next day

9到3之间没有任何值，这是因为该范围必须增大. 9AM到3PM是无效范围.

There will be nothing between 9 and 3, cause the range has to go up. 9AM to 3PM is not a valid range.

为此，您需要一个更复杂的查询...

For this you'll need a more complex query...

类似

->whereRaw("NOT
( 
   trades.tradedate 
   BETWEEN 
   (CONCAT(DATE(trades.tradedate), ' ', exchanges.start_time)) 
   AND 
   (CASE 
       WHEN exchanges.close_time > exchanges.start_time 
           THEN (CONCAT(DATE(DATE_ADD(trades.tradedate, INTERVAL 1 DAY)), ' ', exchanges.close_time))
       ELSE (CONCAT(DATE(trades.tradedate), ' ', exchanges.close_time))
   )
)");

此查询的操作非常简单，它比较日期是否在有效日期之间，如果期间在同一天，则只是追加相同的日期，如果期间不在同一天，则追加用date_add 1天伪造的日期

What this query does is very simple, it compares if the date is between a valid date, if the period is in the same day, it just appends the same date, if the period is not in the same day, it appends a date forged with date_add 1 day

这篇关于whereNotBetween给我错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！