Laravel DB选择语句.查询返回列名作为结果而不是值 [英] Laravel DB select statement. Query returns column name as result rather than values

问题描述

更新:以下内容仅供参考，但我发现这不是问题所在.

UPDATE: The below is for context but I figured out this is not where the problem is.

我是php和laravel的新手.我对使用存储库中的PostgreSQL查询的控制器进行AJAX调用.该查询是一个选择查询，它具有列日期，total_sms和demo(演示是性别Male/Female).

I'm pretty new to php and laravel. I do an AJAX call to a controller that uses a postgresql query from a repository. The query is a select query it has columns dated, total_sms and demo (demo is gender Male/Female).

我有一个数组$ return []以下是一个foreach($ result为$ day). 最后，该数组在返回之前会转换为json.

I have an array $return[] The below is within a foreach ($result as $day). At the end the array is translated to json before returning.

$return[$day->dated][$day->demo] = $day->total_sms;

我希望上面的代码将日期作为一个具有数组值的键，将性别(男性和女性)作为内部数组的键，它们将一个数组作为值，并且这些数组将包含total_sms值. (那句话一定很令人困惑...)

I expected the above to insert the dates as a key with an array value, the gender (Male and Female) as the keys of the inner array, with them having an array as a value and these arrays will contain the total_sms value. (that sentence must have been confusing...)

但是结果是:

{2017-07-03: {d.gender: "0"}, 2017-07-04: {d.gender: "0"}}

我如何构建一个像这样的数组:

How would I build an array like:

{2017-07-03: {Male: "0" , Female: "10"}, 2017-07-04: {Male: "0", Female: "0"}

尽管我认为以上内容实际上并不是我最初的描述方式?我正计划对其进行解码，并将信息粘贴到带有以下内容的chart.js图表​​中:

Although I think the above is not actually how I originally described it? I am planning on decoding it and sticking the information into a chart.js chart with:

$.each(d, function(date, value) {
   window.barChart.data.labels.push(date);
      $.each(d.date, function(demographic, value) {
            // ALWAYS ASCENDING DATA

});

更新:创建问题的代码.

UPDATE: Code that is creating the issue.

我在一个存储库中有一个查询，该查询可以插入不同的参数，以更改使用的人口统计信息.下面是我在pgAdmin中使用的版本，它工作正常.

I have a query in a repository that can have varying parameters inserted, to change what demographic is used. Below is the version I use in pgAdmin, and it works fine.

SELECT d.date dated, count(se.id) total_sms, demo
FROM    (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date, demographic.gender as demo
    FROM generate_series(0, 365, 1) AS offs
    CROSS JOIN (SELECT DISTINCT gender FROM common.profile) AS demographic
    ) d
LEFT OUTER JOIN (
    SELECT id, customer_id, client_report.insert_time, profile.gender, profile.house_income, profile.address AS postcode, profile.age AS age_group, profile.is_employed, profile.is_married, profile.no_children, profile.no_cars, profile.shopping_frequency
    FROM common.client_report
    JOIN common.profile
    ON client_report.profile_id = profile.uuid 
    WHERE sms_status = 'SUCCESS' AND customer_id = 5::int
    ) se
ON (d.date=to_char(date_trunc('day', se.insert_time), 'YYYY-MM-DD')) AND demo = gender
WHERE d.date::date BETWEEN '2017-07-01'::date AND '2017-08-01'::date
GROUP BY d.date, demo
ORDER BY d.date, demo ASC;

任何指出性别，日期或AND customer_id =之后的5都是从命名参数生成的(当我收到错误消息时，我通常可以解决这一问题，当没有错误时，代码可以工作，但给我性别作为列名['de.gender']而不是值['Male'，'Female']).我的意思是说我在参数方面存在错误，不再相信这可能是问题所在.然后，我将因缺少未知的文本转换工具而收到错误消息.我不完全了解这一点，但我知道这意味着我必须使用:: text将性别转换为文本.这样做会导致出现列名.但是，在没有交叉联接的情况下，在另一个更简单的查询中执行此操作，结果正确.这使我相信查询的方式存在问题，或者数据库驱动程序和这些查询存在错误.

Anywhere that states gender, a date or the 5 after AND customer_id = are all generated from named parameters (When I receive an error I can generally fix that, when there is no error, the code works but gives me gender as the column name ['de.gender'] rather than the values ['Male', 'Female']). I mean to say that I have had errors around the parameters, and no longer believe that could be the issue. I then will receive an error around missing a conversion tool for unknown to text. I don't fully understand this but I know it means I have to cast gender as text with ::text. Doing that results in the column name appearing. However doing that in a different, simpler query without the cross join works and the results are correct. This leads me to believe there is an issue with the way my query is or that there is a bug with the DB driver and these queries.

更新:做了一个测试，应该早点做！将不带任何参数的版本放入我的应用程序，使函数运行，并完全按需返回.因此，这源于参数，也可能是定义表的参数，例如de.gender(表为de)

UPDATE: Did a test, should have done it earlier! Placed the version with no parameters into my application, made the function run, and it returns exactly as needed. So this is stemming from parameters, and probably the ones that define tables like de.gender (de being the table)

更新:另一项测试:

CROSS JOIN (SELECT DISTINCT gender FROM common.profile) AS demographic

在这一行上，性别是一个命名参数(:demograph).但是，如果我将其更改为简单的性别，则在使用男性，女性"值时，结果将与预期的一样.如果将其保留为参数，则似乎是在创建许多性别"字符串，而不是获取男性"，女性"值.

On this line, gender is a named parameter (:demograph). However, if I change this to simply gender, the results are as expected with Male, Female values. If it stays as a parameter it seems to be creating lots of strings of 'gender' rather than grabbing the 'Male', 'Female' values.

更新:今天做了很多学习，命名或位置参数仅用于值.意思是所有输入都用双引号引起来，这就是为什么我要创建性别"字符串而不是男性，女性的原因.

UPDATE: Did a lot of learning today, and named or positional parameters are for values only. Meaning all inputs are encased in double quotes, which was why I was creating strings of 'gender' rather than male, female.

推荐答案

我不确定我不确定您的问题

i donn't know i am not sure about your question

$testArray = array(
            "2017-07-03" => array("male" : "0","female" => "10"),
            "2017-07-04" => array("male" : "0","female" => "0")
    );
    return $testArray;

这篇关于Laravel DB选择语句.查询返回列名作为结果而不是值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！