Problems with using custom methods

[WumaCoder]

The incoming field data is not received when using the custom method, and the problem only occurs with groups.

sql:

SELECT DATE_FORMAT(创建时间, '%Y-%m') AS month FROM 夸夸开发者 GROUP BY month ORDER BY month;

result:

DATE_FORMAT:

function DATE_FORMAT(date: any, formatString: string) {
  console.log("date", [date, formatString]);

  if (!(date instanceof DateTime)) date = new DateTime(date);
  const formatMapping: any = {
    "%Y": "YYYY",
    "%y": "YY",
    "%m": "MM",
    "%d": "DD",
    "%H": "HH",
    "%i": "mm",
    "%s": "ss",
  };

  const formattedString = formatString.replace(
    /%[YymdHis]/g,
    (match: string) => {
      return formatMapping[match] || match;
    }
  );

  const formattedDate = dayjs(date).format(formattedString);
  return formattedDate;
}

DATE_FORMAT log:

[mathiasrw]

From the AlaSQLbot

---

The issue you're experiencing with the DATE_FORMAT function in your AlaSQL query seems to be related to how the function interacts with the grouped data. When you use a custom function in AlaSQL along with GROUP BY, the function receives each value from the grouped column. However, your screenshots suggest that DATE_FORMAT isn't receiving the expected date values.

Here are a few steps to troubleshoot and resolve this issue:

1. Check Column Data Types: Ensure the 创建时间 column in your database actually contains date or datetime values. If the data types are incorrect, the DATE_FORMAT function might not receive the expected input.

2. Verify Grouping Behaviour: Sometimes, custom functions behave unexpectedly when used with GROUP BY. Test the DATE_FORMAT function without GROUP BY to see if it works correctly. This can help isolate the issue to the grouping mechanism.

3. Debug the Custom Function: Enhance the logging in your DATE_FORMAT function. Log both the input and the output. This will help you understand what's being passed to the function and what it's returning in both grouped and non-grouped contexts.

4. Review AlaSQL Source Code and Wiki: Check the AlaSQL source code and the AlaSQL wiki for any known limitations or bugs related to custom functions and grouping. There might be specific instructions or workarounds for such cases.

5. Test with Simple Data: Create a simple dataset with known date values and run your query against it. This will help you determine if the issue is with the data or the query itself.

Example Scenario

Suppose you have a table developers with a column creationDate and you want to group the records by month and year of the creation date.

Custom Function for Date Formatting

First, define a custom function DATE_FORMAT that formats dates. This function will take a date and a format string as parameters:

alasql.fn.DATE_FORMAT = function(date, formatString) {
    // Use a date library like dayjs or a custom logic to format the date
    // For example, using dayjs (make sure dayjs is included in your project)
    return dayjs(date).format(formatString);
};

SQL Query with GROUP BY

Next, use this function in your SQL query with GROUP BY:

var sql = `
    SELECT DATE_FORMAT(creationDate, 'YYYY-MM') AS month, COUNT(*) as count
    FROM developers
    GROUP BY DATE_FORMAT(creationDate, 'YYYY-MM')
    ORDER BY month;
`;

// Assuming alasql is properly initialized and your data is loaded
alasql(sql, [], function(results){
    console.log(results);
});

In this query:

• The DATE_FORMAT function is called within the SELECT statement to format the creationDate.
• The same function call is used in the GROUP BY clause to ensure consistency.
• The COUNT(*) is used to get the number of entries per group.

This setup ensures that your custom function works correctly with GROUP BY. Remember to replace dayjs with your actual date formatting logic if you're not using dayjs.

[WumaCoder]

Thank you for your reply.

But I want to group the results using the alias of the select, Can this be achieved?

从 AlaSQLbot

您在 AlaSQL 查询中使用函数时遇到的问题似乎与函数与分组数据的交互方式有关。当您在 AlaSQL 中使用自定义函数时，该函数会从分组列中接收每个值。但是，您的屏幕截图表明它没有收到预期的日期值。DATE_FORMAT``GROUP BY``DATE_FORMAT

以下是排查和解决此问题的几个步骤：

1. 检查列数据类型：确保数据库中的列实际包含日期或日期时间值。如果数据类型不正确，则函数可能无法接收到预期的输入。创建时间``DATE_FORMAT
2. 验证分组行为：有时，自定义函数在与 一起使用时会出现意外行为。测试函数，看看它是否正常工作。这有助于将问题隔离到分组机制。GROUP BY``DATE_FORMAT``GROUP BY
3. 调试自定义函数：增强函数中的日志记录。记录输入和输出。这将帮助您了解传递给函数的内容以及它在分组和非分组上下文中返回的内容。DATE_FORMAT
4. 查看 AlaSQL 源代码和 Wiki：查看 AlaSQL 源代码和 AlaSQL wiki，了解与自定义函数和分组相关的任何已知限制或错误。对于此类情况，可能有特定的说明或解决方法。
5. 使用简单数据进行测试：创建一个具有已知日期值的简单数据集，并针对该数据集运行查询。这将帮助您确定问题出在数据还是查询本身上。

示例方案

假设您有一个包含列的表，并且您希望按创建日期的月份和年份对记录进行分组。developers``creationDate

日期格式设置的自定义函数

首先，定义一个设置日期格式的自定义函数。此函数将采用日期和格式字符串作为参数：DATE_FORMAT

alasql.fn.DATE_FORMAT = function(date, formatString) {
    // Use a date library like dayjs or a custom logic to format the date
    // For example, using dayjs (make sure dayjs is included in your project)
    return dayjs(date).format(formatString);
};

使用 GROUP BY 的 SQL 查询

接下来，在 SQL 查询中使用此函数：GROUP BY

var sql = `
    SELECT DATE_FORMAT(creationDate, 'YYYY-MM') AS month, COUNT(*) as count
    FROM developers
    GROUP BY DATE_FORMAT(creationDate, 'YYYY-MM')
    ORDER BY month;
`;

// Assuming alasql is properly initialized and your data is loaded
alasql(sql, [], function(results){
    console.log(results);
});

在此查询中：

• 在语句中调用该函数以格式化 .DATE_FORMAT``SELECT``creationDate
• 在子句中使用相同的函数调用来确保一致性。GROUP BY
• 用于获取每个组的条目数。COUNT(*)

此设置可确保您的自定义函数在 中正常工作。如果您不使用 .GROUP BY``dayjs``dayjs

[mathiasrw]

Can you give a simpe example of the data, query and result you are hoping for?

[WumaCoder]

I want A and B to have the same effect.

A:

var sql = `
    SELECT DATE_FORMAT(creationDate, 'YYYY-MM') AS month, COUNT(*) as count
    FROM developers
    GROUP BY DATE_FORMAT(creationDate, 'YYYY-MM')
    ORDER BY month;
`;

B:

var sql = `
    SELECT DATE_FORMAT(creationDate, 'YYYY-MM') AS month, COUNT(*) as count
    FROM developers
    GROUP BY month
    ORDER BY month;
`;

var sql = `
    SELECT DATE_FORMAT(creationDate, 'YYYY-MM') AS month, COUNT(*) as count
    FROM developers
    GROUP BY DATE_FORMAT(creationDate, 'YYYY-MM')
    ORDER BY month;
`;

[eerieA]

Hello. After some experimentation I believe this issue is not related to the custom function. It is actually because GROUP BY cannot use the alias, i.e. the "month" column in the example. Here is what I tested using JSFiddle: https://jsfiddle.net/ninjaqwert/1hgexo7a/86/ As can be seen from the last test sql (var sql5), GROUP BY cannot recognize the alias "month" when there is no custom function.

However, this behavior of alasql is consistent with conventional SQL. In the SQL we know, GROUP BY is processed before SELECT, therefore it does not know any aliases set with SELECT. This is what I tried using Oracle Live SQL:

Hence, if I understand the OP correctly, the desired behaviour above is in fact not an expected one according to normal SQL rules. I am not sure how the owners want to deal with this though.