Sql Query To Blesta Friendly Record

[activa]

Hello

 

i have a mysql query code work perfectly , but i want to convert it to a blesta query standard .

				SELECT
				  date_transaction,
				  IFNULL( SUM(amount), 0 ) AS daily_sale
				FROM transactions
				WHERE date_transaction BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW()
				group by DATE(`date_transaction`)

something like

 

		$this->Record->select(array('date_transaction , IFNULL( SUM(amount), 0 )'=>"daily_sale"), false)->
			from("transactions");
			
		$this->Record->query("
				WHERE date_transaction BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW()
				group by DATE(`date_transaction`)
		
		")->fetchAll();
		

​

 

 

[Tyson]

$this->Record->query("
                                SELECT
				  date_transaction,
				  IFNULL( SUM(amount), ? ) AS daily_sale
				FROM transactions
				WHERE date_transaction BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) and NOW()
				group by DATE(`date_transaction`)
", 0, 7);

If you're using php, you might as well set specific dates to search between rather than using MySQL's functions like BETWEEN, DATE_SUB, and NOW. Also, I'm not sure why you are grouping on a column passed to the DATE function.

[activa]

  On 11/6/2015 at 12:47 AM, Tyson said:

$this->Record->query("
                                SELECT
				  date_transaction,
				  IFNULL( SUM(amount), ? ) AS daily_sale
				FROM transactions
				WHERE date_transaction BETWEEN DATE_SUB(NOW(), INTERVAL ? DAY) and NOW()
				group by DATE(`date_transaction`)
", 0, 7);

If you're using php, you might as well set specific dates to search between rather than using MySQL's functions like BETWEEN, DATE_SUB, and NOW. Also, I'm not sure why you are grouping on a column passed to the DATE function.

 

 

 

Hello Tyson

 

the grouping by date to get what my client need . my code work perfectly and give me what i'm really need .

 

the my question was is possible to convert the sql query to the normal query recod used by blesta .

 

$this->Record->where() , $this->Record->order() , $this->Record->select()  ....

 

also i noticed that the Record class didn't support the operator "between" , like

$this->Record->where("date", "between" , $From AND $To ) ;

[Tyson]

  On 11/6/2015 at 12:00 PM, activa said:

the grouping by date to get what my client need . my code work perfectly and give me what i'm really need .

 

Your example groups by the function DATE on `date_transaction`. Grouping on a date field is fine, but running it through the DATE function is redundant. I'm actually surprised that would work anyway considering DATE is not a supported aggregate function for the GROUP BY clause.

 

Additionally, the field you select and group by, date_transaction, does not exist. I think you're referring to the date_added column?

 

 

  On 11/6/2015 at 12:00 PM, activa said:

the my question was is possible to convert the sql query to the normal query recod used by blesta .

 

$this->Record->where() , $this->Record->order() , $this->Record->select()  ....

 

Yes, it is possible to convert it into a query using the Record component.

 

 

  On 11/6/2015 at 12:00 PM, activa said:

also i noticed that the Record class didn't support the operator "between" , like

$this->Record->where("date", "between" , $From AND $To ) ;

 

BETWEEN is a clause, not an operator. You could still create a BETWEEN clause with the Record component, but based on your example, I think it would be simpler to instead create two conditionals in the WHERE clause to filter by date.

 

Your query is very similar to the Transactions Received report under [billing] -> [Reports]. However, the query you posted is not valid because the `transactions` table contains no `date_transaction` column. What is the exact query you are running?

[activa]

  On 11/6/2015 at 6:43 PM, Tyson said:

Your example groups by the function DATE on `date_transaction`. Grouping on a date field is fine, but running it through the DATE function is redundant. I'm actually surprised that would work anyway considering DATE is not a supported aggregate function for the GROUP BY clause.

 

Additionally, the field you select and group by, date_transaction, does not exist. I think you're referring to the date_added column?

 

 

 

Yes, it is possible to convert it into a query using the Record component.

 

 

 

BETWEEN is a clause, not an operator. You could still create a BETWEEN clause with the Record component, but based on your example, I think it would be simpler to instead create two conditionals in the WHERE clause to filter by date.

 

Your query is very similar to the Transactions Received report under [billing] -> [Reports]. However, the query you posted is not valid because the `transactions` table contains no `date_transaction` column. What is the exact query you are running?

 

 

Thank tyson again ...

 

this query has nothing with the transactions table . is our own table .

can you post a converstion using using the Record component. .

[Tyson]

Is this for Blesta?

[activa]

  On 11/7/2015 at 10:07 PM, Tyson said:

Is this for Blesta?

 

yes .

[Tyson]

I'd probably do this

$now = date('c');
$days_ago = date('c', strtotime($now . ' -7 days'));

$results = $this->Record->select(array('date_transaction'))
    ->select(array('IFNULL(SUM(amount),?' => 'daily_sale'), false)
    ->appendValues(0)
    ->from('transactions')
    ->where('date_transaction', '>=', $days_ago)
    ->where('date_transaction', '<=', $now)
    ->group(array('date_transaction'))
    ->fetchAll();

[activa]

  On 11/10/2015 at 5:25 AM, Tyson said:

 

I'd probably do this

$now = date('c');
$days_ago = date('c', strtotime($now . ' -7 days'));

$results = $this->Record->select(array('date_transaction'))
    ->select(array('IFNULL(SUM(amount),?' => 'daily_sale'), false)
    ->appendValues(0)
    ->from('transactions')
    ->where('date_transaction', '>=', $days_ago)
    ->where('date_transaction', '<=', $now)
    ->group(array('date_transaction'))
    ->fetchAll();

 

thanks i will test it and if it working i will use it

 

best regards