Mysql 动态值作为别名 [英] Mysql dyanmic value as alias
问题描述
所以下面的作品
$query = "SELECT
A.entity_id,
B.value AS variable_character,
C.value AS text,
D.value AS integer
FROM customer_address_entity AS A
LEFT JOIN customer_address_entity_varchar AS B
ON B.entity_id = A.entity_id
LEFT JOIN customer_address_entity_text AS C
ON C.entity_id = A.entity_id
LEFT JOIN customer_address_entity_int AS D
ON A.entity_id = D.entity_id
ORDER BY A.entity_id DESC
LIMIT 100";
然而,有许多值需要连接到表 A 中的实体,现在它们都基于新值创建自己的嵌套数组.
However there are many values that need to be joined to the entity in table A and right now they are all creating their own nested array based on the new value.
0 =>
array (size=4)
'entity_id' => string '597424' (length=6)
'variable_character' => string 'Dave' (length=4)
'text' => string '45 Haven Rd' (length=11)
'intiger' => string '43' (length=2)
1 =>
array (size=4)
'entity_id' => string '597424' (length=6)
'variable_character' => null
'text' => string '45 Haven Rd' (length=11)
'intiger' => string '43' (length=2)
2 =>
array (size=4)
'entity_id' => string '597424' (length=6)
'variable_character' => string 'Danielson' (length=9)
'text' => string '45 Haven Rd' (length=11)
'integer' => string '43' (length=2)
3 => etc
...
我认为这是由于尝试加入相同的键名,因此我想使用更动态的东西,例如另一个值,例如 B.attribute_type_id 的值.在那里我可以得到一个看起来像
Im thinking this is due to the same key name trying to be joined and therefore I would like to use something more dynamic such as another value such as the value of B.attribute_type_id. where I could get an array to look like
array (size=7)
'entity_id' => string '597424' (length=6)
'1' => null
'2' => 'Dave'
'3' => 'Danielson'
'4' => '45 Haven Road'
'text' => string '45 Haven Rd' (length=11)
'intiger' => string '43' (length=2)
甚至更好:属性的标题位于另一个名为 eav_attribute 的表中,真正理想的是类似于
Or even better: the title for the attributes live in another table called eav_attribute and what would really be ideal would be something along the lines of
$query = "SELECT
A.entity_id,
B.value AS (SELECT attribute_code FROM eav_attribute WHERE attribute_id = B.attribute_id),
C.value AS text,
...
这样的事情可能吗?还是我以错误的方式处理这个问题?
Is something like this possible? Or am I going about this in the wrong way?
推荐答案
为什么使用左联接而不是内联接?另外,如果您正在进行动态查询.您应该考虑使用存储过程.您可以将 SQL 语句挂载到变量中,然后执行.这是一个简单的例子:
Why are you using left join instead of inner join? Also, if you are doing dynamic queries. You should consider using Stored Procedures. You could mount the SQL statement in a variable and then execute. Here's a quick example:
CREATE DEFINER=`YourDBUSER`@`localhost` PROCEDURE `rtYourProcedureName`(IN variable1 int,variable2d varchar(100), sortBy varchar(50), startRow int, ThisSQL varchar(5000), OUT totalRows int)
BEGIN
###start: MySQL is dumb and do not allow default parameter values, set here:
SET @variable1 = IFNULL(variable1 ,'0');
SET @variable2 = IFNULL(variable2 ,'');
SET @sortBy = IFNULL(sortBy ,'');
SET @startRow = IFNULL(startRow ,1);
###end: MySQL is dumb and do not allow default parameter values
set @htwsql = ThisSQL;
set @htwsql = ' SELECT SQL_CALC_FOUND_ROWS STRAIGHT_JOIN t1.field1 ,t2.field2 ';
if @variable1 = 0 then
set @htwsql := concat(@htwsql, ' from table1 t1');
else
set @htwsql := concat(@htwsql,' from table2 t2 w INNER JOIN table1 t1 ');
end if;
set @htwsql := concat(@htwsql, ' where 1 = 1 ');
### sort order
if rtrim(ltrim(@sortBy)) <> '' then
set @htwsql := concat(@htwsql,char(13), ' ORDER BY ' , cast(@sortBy as char(50)));
end if;
### limit records for pagination
set @htwsql := concat(@htwsql,char(13), ' LIMIT ', StartRow-1 ,',',24);
### just to debug the generated SQL
insert into rtlogtmp (log) select concat ('rtYourProcedureName SQL = ',@htwsql);
PREPARE stmt1 FROM @htwsql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SELECT FOUND_ROWS() into totalRows;
END
ps:小心您用作别名的名称.它们可能是保留的 SQL 语法.
p.s.: Be careful with the names you use as alias. They may be reserved SQL syntax.
这篇关于Mysql 动态值作为别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!