Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for selecting from an ad-hoc table with parameterized data? #152

Open
freakingawesome opened this issue Oct 9, 2018 · 7 comments
Milestone

Comments

@freakingawesome
Copy link
Contributor

I often come across the need to pass in a "table" of data into a query. In the past I've done string concatenation to create a temporary table (or table variable), then added an INSERT with multiple rows of values. After playing with sqlkata for some time (which I love, by the way), I think there could be a more intuitive way of building such a temporary table, and without actually building a temporary table.

For example, both SQL Server and PostgreSQL allow arbitrary tables of the form:

SQL Server:

SELECT a, b, c
FROM (
    VALUES
        (1, 2, 3),
        (4, 5, 6)
    ) AS a (a, b, c)

PostgreSQL:

WITH cte (a, b, c) AS (
    VALUES
        (1, 2, 3),
        (4, 5, 6)
)
SELECT a, b, c
FROM cte;

Both the above queries return a table of the following form:

a b c
1 2 3
4 5 6

To me, this feels like a perfect fit for sqlkata, as a subquery and/or inside a CTE. It has the feel of Insert() syntax. Perhaps it could be expressed as something like this?

var cols = new [] {"Name", "Price"};

var data = new [] {
    new object[] { "A", 1000 },
    new object[] { "B", 2000 },
    new object[] { "C", 3000 },
};

var query = new Query(cols, data);

If there is interest in this idea, I would happily take a stab at building it out and submitting a PR (it is Hacktoberfest, after all!).

@freakingawesome
Copy link
Contributor Author

For those databases that don't support ad-hoc queries via VALUES, we can rely on UNION ALL:

SELECT 1 AS a, 2 AS b, 3 AS c
UNION ALL
SELECT 4, 5, 6;

@ahmad-moussawi
Copy link
Contributor

Thanks for the suggestion, I think the WithRaw method can fit well here https://sqlkata.com/docs/cte#withraw

@freakingawesome
Copy link
Contributor Author

Or perhaps just an overload of With with the following signature?

With(string alias, IEnumerable<string> columns, IEnumerable<object> values)

since WithRaw seems more geared towards non-generated, platform-specific SQL?

@ahmad-moussawi
Copy link
Contributor

ahmad-moussawi commented Oct 17, 2018

@freakingawesome I think you mean

With(string alias, IEnumerable<string> columns, IEnumerable<IEnumerable<object>> values)

right ?

@freakingawesome
Copy link
Contributor Author

Both should arguably be supported. If we were to model it based off the AsInsert() variations (those that don't involve a Query source), then we'd have the following overloads:

With(object data)
With(IEnumerable<string> columns, IEnumerable<object> values)
With(IReadOnlyDictionary<string, object> data)
With(IEnumerable<string> columns, IEnumerable<IEnumerable<object>> valuesCollection)

@ahmad-moussawi
Copy link
Contributor

If I get it well, we need to load more than one row to simulate the matrix,

a b c
1 2 3
4 5 6

the following overloads allows us to insert just one row:

With(object data)
With(IEnumerable<string> columns, IEnumerable<object> values)
With(IReadOnlyDictionary<string, object> data)

so we only have this overload that accept a list of list

With(IEnumerable<string> columns, IEnumerable<IEnumerable<object>> valuesCollection)

as a valid use case

@freakingawesome
Copy link
Contributor Author

My mistake - you're absolutely right. I had thought the other overloads were doing more reflection introspection than they actually are. I agree that the only overload we'd need is

With(IEnumerable<string> columns, IEnumerable<IEnumerable<object>> valuesCollection)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants