SELECT * FROM t_report WHERE date_part('year', report_time) = 2024

[silentbody]

"SELECT * FROM t_report WHERE date_part('year', report_time) = 2024
How is this SQL implemented?"

[jeffgbutler]

It's easy to add new functions to the library. Here's an example that should work:

public class DatePart extends AbstractTypeConvertingFunction<Date, Integer, DatePart> {

    private final String part;
    protected DatePart(String part, BindableColumn<Date> column) {
        super(column);
        this.part = part;
    }

    @Override
    public Optional<JDBCType> jdbcType() {
        return Optional.of(JDBCType.INTEGER);
    }

    @Override
    public FragmentAndParameters render(RenderingContext renderingContext) {
        return column.render(renderingContext)
                .mapFragment(f -> "date_part('" + part + "', " + f + ")");
    }

    @Override
    protected DatePart copy() {
        return new DatePart(part, column);
    }

    public static DatePart date_part(String part, BindableColumn<Date> column) {
        return new DatePart(part, column);
    }
}

[silentbody]

Thank you very much! This way of handling the code is very elegant!

SelectStatementProvider statement = select(DatePart.date_part("'year'", reportTime))
                .from(report)
                .where(memberId, isEqualTo(_memberId))
                .and(deleted, isEqualTo(false))
                .groupBy(reportTime)
                .orderBy(reportTime.descending())
                .build()
                .render(RenderingStrategies.MYBATIS3);

Optional<WhereClauseProvider> whereClause = where(memberId, isEqualToWhenPresent(query.getMemberId()))
                .and(parseStatus, isEqualToWhenPresent(query.getParseStatus()))
                .and(reportType, isEqualToWhenPresent(query.getReportType()))
                .and(DatePart.date_part("'year'", reportTime), isEqualToWhenPresent(query.getYear()))
                .and(deleted, isEqualTo(false))
                .build()
                .render(RenderingStrategies.MYBATIS3);

[silentbody]

But there is another problem, which is a bit more complex, regarding the function 'row_number() over (partition by report_time desc, created desc)'.

@Select({
        "select * from (",
        "  select row_number() over (partition by name order by report_time desc, created desc) as rn,",
        "    t.*",
        "  from t_report t",
        "    ${whereClause}",
        ") tmp",
        "  where tmp.rn = 1",
        "  order by tmp.report_time desc, tmp.created desc",
})
@ResultMap("ReportResult")
List<Report> findLastReportGroupByName(WhereClauseProvider whereClause);

[jeffgbutler]

This is more complex, but you should be able to make it happen:

1. Build a "model" class that implements BasicColumn and will render as row_number() over (...)
2. Build some DSL code that will construct the model class with a syntax you are comfortable with