Skip to content

SelectClause

Jump to bottom
MSugiura edited this page Jan 21, 2024 · 8 revisions

Select columns and constants can be set in the Select clause. It is also possible to give it a different name.

Select

There are multiple ways to specify the Select clause, but the most frequent pattern is column selection.

In addition to column selection, it supports constants, calculation formulas, and inline queries.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(a, "a_id");
        sq.Select(a, "value").As("val");
        sq.Select(10).As("val_10");
        Console.WriteLine(sq.ToText());
    }
}

Column(ColumnValue)

If you want to select a column, set the From clause and get the SelectableTable in advance.

Then use the Select method to select the table and column names. You can specify an alias name by using the chain method As.

Alias name is optional.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(a, "a_id");
        sq.Select(a, "value").As("val");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    a.a_id,
    a.value AS val
FROM
    table_a AS a

NOTE: The above description is an abbreviation. To be verbose, you need to use the ColumnValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
		var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(new ColumnValue(a, "a_id")).As("a_id");
        sq.Select(new ColumnValue(a, "value")).As("val");
        Console.WriteLine(sq.ToText());
    }
}

SelectAll

Use the SelectAll method to select all columns. Please specify the SelectableTable class as the first argument.

using System;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("tabla_a").As("a");
        sq.SelectAll(a);
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    a.*
FROM
    tabla_a AS a

Used without arguments to create a wildcard selection query. This method is not recommended because column information will be missing, making it difficult to reuse the same instance.

Constant

SelectableTable is not used when selecting constants.

Write the value you want to select in the Select method as is, and also specify an alias name using method chaining.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(1).As("v1");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    1 AS v1

NOTE: The above description is an abbreviation. To be verbose, you need to use the LiteralValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new LiteralValue("1")).As("v1");
        Console.WriteLine(sq.ToText());
    }
}

Parameter

You need to register parameters using the AddParameter method in advance. The return is the parameter name and the type is string. Please note that Carbunql does not manage placeholder identifiers, so you need to specify an identifier that matches the execution environment.

You can then select by passing the parameter name to the Select method.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var prm = sq.AddParameter(":prm", 1);
        sq.Select(prm).As("val");
        Console.WriteLine(sq.ToText());
    }
}
/*
  :prm = 1
*/
SELECT
    :prm AS val

Inline query

Select queries can be used as inline queries by using the ToValue method.

using System;
using Carbunql;
using Carbunql.Building;
                    
public class Program
{
    public static void Main()
    {
        var x = new SelectQuery("select x.value from table_x"); 
		
        var sq = new SelectQuery();
        sq.Select(x.ToValue()).As("val");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    (
        SELECT
            x.value
        FROM
            table_x
    ) AS val

Formula

You can add operators and operands using the AddOperatableValue method of the ValueBase class. Note that added expressions are always added at the end.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(() => {
            var v = new LiteralValue("1");
            v.AddOperatableValue("+", new ColumnValue(f, "value1"));
            v.AddOperatableValue("-", new ColumnValue(f, "value2"));
            return v;
        }).As("val");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    1 + a.value1 - a.value2 AS val
FROM
    tabla_a AS a

Grouping

If you want to use parentheses, use the ToGroup method.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("tabla_a").As("a");
        sq.Select(() => {
            var v1 = new LiteralValue("1");
            
            var v2 = new ColumnValue(f, "value1");
            v2.AddOperatableValue("-", new ColumnValue(f, "value2"));
            
            v1.AddOperatableValue("*", v2.ToGroup());
      
            return v1;
        }).As("val");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    1 * (a.value1 - a.value2) AS val
FROM
    tabla_a AS a

Function

Express it using the FunctionValue class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new FunctionValue("now")).As("dt");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    NOW() AS dt

Arguments

Specify the arguments as follows.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        sq.Select(new FunctionValue("concat", new ValueCollection { "'a'", "'b'", "'c'" })).As("txt");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    CONCAT('a', 'b', 'c') AS txt

Window function

It can be defined using the OverClause class.

using System;
using Carbunql;
using Carbunql.Building;
using Carbunql.Values;
using Carbunql.Clauses;
                    
public class Program
{
    public static void Main()
    {
        var sq = new SelectQuery();
        var (f, a) = sq.From("table_a").As("a");
        sq.Select(new FunctionValue("row_number",
            () =>
            {
                var ov = new OverClause();
                ov.AddPartition(new ColumnValue(a, "name"));
                ov.AddOrder(new ColumnValue(a, "a_id").ToSortable());
                return ov;
            }
        )).As("row_num");
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    ROW_NUMBER() OVER(
        PARTITION BY
            a.name
        ORDER BY
            a.a_id
    ) AS row_num
FROM
    table_a AS a

Window clause

It is possible to write, but it is on hold because it is very redundant and the specifications are likely to change.

Subquery and Select method

SelectableTable holding column information like a subquery If you specify this in the Select method, all columns will be selected. At this time, the "*" keyword is not used, and each column is expanded.

using System;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var x = new SelectQuery("select a.id, a.value from table_a as a");
		
        var sq = new SelectQuery();
		var (f, a) = sq.From(x).As("a");
        sq.Select(a);
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    a.id,
    a.value
FROM
    (
        SELECT
            a.id,
            a.value
        FROM
            table_a AS a
    ) AS a

Duplicate column names

When subqueries are joined to tables, duplicate column names may occur. In this case, the one written first takes precedence by default.

using System;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var tableA = new SelectQuery("select a.a_id, a.value from table_a as a");
        var tableB = new SelectQuery("select b.a_id, b.value, b.remarks from table_b as b");

        var sq = new SelectQuery();
        var (f, a) = sq.From(tableA).As("a");
        var b = f.InnerJoin(tableB).As("b").On(a, "a_id");

        sq.Select(a);
        sq.Select(b);

        Console.WriteLine(sq.ToText());
    }
}
SELECT
    a.a_id,
    a.value,
    b.remarks
FROM
    (
        SELECT
            a.a_id,
            a.value
        FROM
            table_a AS a
    ) AS a
    INNER JOIN (
        SELECT
            b.a_id,
            b.value,
            b.remarks
        FROM
            table_b AS b
    ) AS b ON a.a_id = b.a_id

GetColumnNames

You can obtain a list of selected column names using the GetColumnName method. If an alias name is specified for the column, the alias name is retrieved.

using System;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var tableA = new SelectQuery("select a.a_id, a.value from table_a as a");
        var tableB = new SelectQuery("select b.a_id, b.value, b.remarks from table_b as b");

        var sq = new SelectQuery();
        var (f, a) = sq.From(tableA).As("a");
        var b = f.InnerJoin(tableB).As("b").On(a, "a_id");

        sq.Select(a);
        sq.Select(b);

        foreach (var x in sq.GetColumnNames()) 
        {
            Console.WriteLine(x);
        }
    }
}
a_id
value
remarks

GetSelectableItems

If you want to get the selected items rather than just the column name, use GetSelectableItems.

using System;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var tableA = new SelectQuery("select a.a_id, a.value from table_a as a");
        var tableB = new SelectQuery("select b.a_id, b.value, b.remarks from table_b as b");

        var sq = new SelectQuery();
        var (f, a) = sq.From(tableA).As("a");
        var b = f.InnerJoin(tableB).As("b").On(a, "a_id");

        sq.Select(a);
        sq.Select(b);

        foreach (var x in sq.GetSelectableItems()) 
        {
            Console.WriteLine(x.ToText());
        }
    }
}
a.a_id
a.value
b.remarks

Remove SelectableItem

SelectableItem is stored in SelectClause. You can remove it by specifying the Remove method.

using System;
using System.Linq;
using Carbunql;
using Carbunql.Building;

public class Program
{
    public static void Main()
    {
        var tableA = new SelectQuery("select a.a_id, a.value from table_a as a");
        var tableB = new SelectQuery("select b.a_id, b.value, b.remarks from table_b as b");

        var sq = new SelectQuery();
        var (f, a) = sq.From(tableA).As("a");
        var b = f.InnerJoin(tableB).As("b").On(a, "a_id");

        sq.Select(a);
        sq.Select(b);

        var item = sq.GetSelectableItems().Where(x => x.Alias == "a_id").First();
        sq.SelectClause!.Remove(item);
        
        Console.WriteLine(sq.ToText());
    }
}
SELECT
    a.value,
    b.remarks
FROM
    (
        SELECT
            a.a_id,
            a.value
        FROM
            table_a AS a
    ) AS a
    INNER JOIN (
        SELECT
            b.a_id,
            b.value,
            b.remarks
        FROM
            table_b AS b
    ) AS b ON a.a_id = b.a_id
Clone this wiki locally