[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Fmsystem-commits] [17192] optimize sql
|
From: |
sigurdne |
|
Subject: |
[Fmsystem-commits] [17192] optimize sql |
|
Date: |
Tue, 24 Oct 2017 09:20:24 -0400 (EDT) |
Revision: 17192
http://svn.sv.gnu.org/viewvc/?view=rev&root=fmsystem&revision=17192
Author: sigurdne
Date: 2017-10-24 09:20:24 -0400 (Tue, 24 Oct 2017)
Log Message:
-----------
optimize sql
Modified Paths:
--------------
trunk/property/inc/class.soentity.inc.php
trunk/property/inc/class.soinvoice.inc.php
trunk/property/inc/class.solocation.inc.php
Modified: trunk/property/inc/class.soentity.inc.php
===================================================================
--- trunk/property/inc/class.soentity.inc.php 2017-10-24 11:14:30 UTC (rev
17191)
+++ trunk/property/inc/class.soentity.inc.php 2017-10-24 13:20:24 UTC (rev
17192)
@@ -2803,11 +2803,11 @@
{
$location_id =
$GLOBALS['phpgw']->locations->get_id($this->type_app[$type],
".{$type}.{$entry['entity_id']}.{$entry['cat_id']}");
- $sql = "SELECT count(*) as hits
FROM fm_bim_item WHERE location_id = {$location_id} AND p_location_id =
{$p_location_id} AND p_id = '{$p_id}'";
+ $sql = "SELECT count(id) as
hits FROM fm_bim_item WHERE location_id = {$location_id} AND p_location_id =
{$p_location_id} AND p_id = '{$p_id}'";
}
else
{
- $sql = "SELECT count(*) as hits
FROM fm_{$type}_{$entry['entity_id']}_{$entry['cat_id']} WHERE p_entity_id =
{$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
+ $sql = "SELECT count(id) as
hits FROM fm_{$type}_{$entry['entity_id']}_{$entry['cat_id']} WHERE p_entity_id
= {$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
}
$this->db->query($sql, __LINE__,
__FILE__);
@@ -2834,7 +2834,7 @@
}
}
- $sql = "SELECT count(*) as hits FROM fm_tts_tickets
WHERE p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num =
'{$p_id}'";
+ $sql = "SELECT count(id) as hits FROM fm_tts_tickets
WHERE p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num =
'{$p_id}'";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -2852,7 +2852,7 @@
);
}
- $sql = "SELECT count(*) as hits FROM fm_request WHERE
p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
+ $sql = "SELECT count(id) as hits FROM fm_request WHERE
p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -2870,7 +2870,7 @@
);
}
- $sql = "SELECT count(*) as hits FROM fm_project WHERE
p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
+ $sql = "SELECT count(id) as hits FROM fm_project WHERE
p_entity_id = {$entity_id} AND p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -2886,7 +2886,7 @@
);
}
- $sql = "SELECT count(*) as hits FROM fm_s_agreement
{$this->join} fm_s_agreement_detail ON fm_s_agreement.id =
fm_s_agreement_detail.agreement_id WHERE p_entity_id = {$entity_id} AND
p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
+ $sql = "SELECT count(fm_s_agreement.id) as hits FROM
fm_s_agreement {$this->join} fm_s_agreement_detail ON fm_s_agreement.id =
fm_s_agreement_detail.agreement_id WHERE p_entity_id = {$entity_id} AND
p_cat_id = {$cat_id} AND p_num = '{$p_id}'";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
Modified: trunk/property/inc/class.soinvoice.inc.php
===================================================================
--- trunk/property/inc/class.soinvoice.inc.php 2017-10-24 11:14:30 UTC (rev
17191)
+++ trunk/property/inc/class.soinvoice.inc.php 2017-10-24 13:20:24 UTC (rev
17192)
@@ -446,7 +446,7 @@
. " {$this->left_join} fm_project ON
fm_workorder.project_id = fm_project.id"
. " {$this->join} fm_vendor ON
$table.spvend_code = fm_vendor.id $filtermethod";
- $this->db->query('SELECT count(*) AS cnt ' .
substr($sql, strripos($sql, ' FROM')), __LINE__, __FILE__);
+ $this->db->query("SELECT count({$table}.id) AS cnt " .
substr($sql, strripos($sql, ' FROM')), __LINE__, __FILE__);
$this->db->next_record();
$this->total_records = $this->db->f('cnt');
Modified: trunk/property/inc/class.solocation.inc.php
===================================================================
--- trunk/property/inc/class.solocation.inc.php 2017-10-24 11:14:30 UTC (rev
17191)
+++ trunk/property/inc/class.solocation.inc.php 2017-10-24 13:20:24 UTC (rev
17192)
@@ -100,11 +100,11 @@
$this->db->query("SELECT id as
bim_type FROM fm_bim_type WHERE location_id = {$location_id}", __LINE__,
__FILE__);
$this->db->next_record();
$bim_type =
(int)$this->db->f('bim_type');
- $sql = "SELECT count(*) as hits
FROM fm_bim_item WHERE location_code {$condition} AND type = {$bim_type}";
+ $sql = "SELECT count(id) as
hits FROM fm_bim_item WHERE location_code {$condition} AND type = {$bim_type}";
}
else
{
- $sql = "SELECT count(*) as hits
FROM fm_{$type}_{$entry['entity_id']}_{$entry['cat_id']} WHERE location_code
{$condition}";
+ $sql = "SELECT count(id) as
hits FROM fm_{$type}_{$entry['entity_id']}_{$entry['cat_id']} WHERE
location_code {$condition}";
}
$this->db->query($sql, __LINE__,
__FILE__);
@@ -129,7 +129,7 @@
}
}
- $sql = "SELECT count(*) as hits FROM fm_tts_tickets
WHERE location_code {$condition}";
+ $sql = "SELECT count(id) as hits FROM fm_tts_tickets
WHERE location_code {$condition}";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -144,7 +144,7 @@
);
}
- $sql = "SELECT count(*) as hits FROM fm_request WHERE
location_code {$condition}";
+ $sql = "SELECT count(id) as hits FROM fm_request WHERE
location_code {$condition}";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -159,7 +159,7 @@
);
}
- $sql = "SELECT count(*) as hits FROM fm_project WHERE
location_code {$condition}";
+ $sql = "SELECT count(id) as hits FROM fm_project WHERE
location_code {$condition}";
$this->db->query($sql, __LINE__, __FILE__);
$this->db->next_record();
if ($this->db->f('hits'))
@@ -1005,7 +1005,7 @@
if (!$cache_info)
{
- $this->db->query('SELECT count(*) AS cnt ' .
substr($sql, strripos($sql, ' from')), __LINE__, __FILE__);
+ $this->db->query("SELECT
count(fm_location{$type_id}.location_code) AS cnt " . substr($sql,
strripos($sql, ' from')), __LINE__, __FILE__);
$this->db->next_record();
$cache_info = array
@@ -1557,7 +1557,7 @@
}
}
- $sql = "SELECT $parent_table.location_code
,count(*) as count_99 FROM $paranthesis fm_location$type_id $joinmethod where
fm_location$type_id.status=2 group by $parent_table.location_code ";
+ $sql = "SELECT $parent_table.location_code
,count(fm_location{$type_id}.location_code) as count_99 FROM $paranthesis
fm_location$type_id $joinmethod where fm_location$type_id.status=2 group by
$parent_table.location_code ";
$this->db->query($sql, __LINE__, __FILE__);
while ($this->db->next_record())
@@ -1565,7 +1565,7 @@
$outdated[$this->db->f('location_code')]['count_99'] = $this->db->f('count_99');
}
- $sql = "SELECT $parent_table.location_code
,count(*) as count_all FROM $paranthesis fm_location$type_id $joinmethod group
by $parent_table.location_code ";
+ $sql = "SELECT $parent_table.location_code
,count(fm_location{$type_id}.location_code) as count_all FROM $paranthesis
fm_location$type_id $joinmethod group by $parent_table.location_code ";
$this->db->query($sql, __LINE__, __FILE__);
while ($this->db->next_record())
{
@@ -1893,7 +1893,7 @@
$table = 'fm_location' . $type_id . '_history';
- $sql = "SELECT count(*) AS cnt FROM $table WHERE
location_code='$location_code'";
+ $sql = "SELECT count(location_code) AS cnt FROM $table
WHERE location_code='$location_code'";
$this->db->query($sql, __LINE__, __FILE__);
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Fmsystem-commits] [17192] optimize sql,
sigurdne <=