Use own struct for dest

[developer2346]

Hi Is it possible to use my own struct as the dest to stmt.Query(db, &dest)? Currently as the dest is different than my domain struct, I have to copy all dest's fields to my own struct(domain struct) and this isn't performant.

Like:

type MyPerson []struct {
	FirstName string 
	LastName  string
	Comments  []struct {
		Title string
		Body  string
	}
}

Currently is:

type dest []struct {
	model.Person
	Comments []struct {
		model.Comment
	}
}

[go-jet]

Hi @developer2346 , Yeah, it is possible, check the wiki: https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM)#custom-model-types https://github.com/go-jet/jet/wiki/FAQ#scan-stopped-working-after-naming-a-destination-type

[developer2346]

Nice Job! But seems if I remove sql:"primary_key" tag, it does not work correctly. Same happens when I don't SELECT the ID column

[go-jet]

Yeah, for complex(nested) structs, sql:"primary_key" tag is necessary and primary column has to be selected, otherwise query result mapping wouldn't know how to map query result.

[developer2346]

Is there any way to use Jet without its query builder? I just need it for QRM

[houten11]

https://github.com/go-jet/jet/wiki/Statements#raw-statements

[developer2346]

I have the following SQL schema:

CREATE TABLE `user`
(
    `username`    VARCHAR(20) NOT NULL ,
    `first_name` text NOT NULL ,
    `last_name` text NOT NULL ,
    `password` text NOT NULL ,
    `article_add` boolean NULL ,
    `article_edit` boolean NULL ,
    `article_delete` boolean NULL ,
PRIMARY KEY (`username`)
);

My target struct:

type User struct {
	Username    string     `sql:"primary_key"`
	FirstName    string    
	LastName    string   
	Password     string   
	Permissions  Permission
}
type Permission struct {
	AddArticale    bool `json:"article_add"`
	EditArticle       bool `json:"article_edit"`
	DeleteArticale bool `json:"article_delete"`
}

but I am unable to unwrap the SELECT * from user query into the user struct. I have tested several ways ... Would you mind to help please? thanks in advance!

[houten11]

From the raw statements wiki:

Every projection has to be aliased in destination type name.field name format.

So, you can't use *, you have to alias each column yourself:

SELECT user.username as "user.username",
       user.first_name as "user.first_name",
       ....
FROM user

[developer2346]

The issue is permissions. They are not mapped. I also tried something like SELECT article_edit as user.permissions.editArticle ...

[houten11]

Alias should be just destination type name.field name:

SELECT article_edit as "permission.editArticle" -- permission not permissions or user.permissions

[developer2346]

Also how to use ORDER_BY for dynamic conditions? the order is user input and user can pass empty order.

Also how zero-values are handled for WHERE clauses? As the value to WHERE is user input and user can pass a zero-value

[go-jet]

Check FAQ for dynamic projection list and condition. The same approach can be used for ORDER_BY. nil values passed to WHERE clause, means WHERE clause is omitted. You can check generated sql using stmt.Sql() or stmt.DebugSql()

[developer2346]

I am trying to use LIKE operator with RawStatement:

if q != "" {
    has = `article.title LIKE ` + "'%" + "$s" + `%'`
    args["$s"] = q
}

but it adds extra ' also says: jet: sql: expected 0 arguments, got 1

[houten11]

There is no need to wrap the parameter with '.

if q != "" {
    has = `article.title LIKE $s`
    args["$s"] = "%" + q + "%"
}

[developer2346]

Just to ensure let me ask something I am using RawStatement and RawArgs then stmt.QueryContext. Is it sql-injection safe? I mean does RawArgs, escape the injections?

an example of what I am doing:

stmt := j.RawStatement("SELECT * FROM `users` WHERE `id`=$id", j.RawArgs{"$id": id})
err := stmt.QueryContext(ctx, u.sql, &user)

as far as I know MySQL doesn't support named arguments for QueryContext and also I am not using prepared statements so I have doubt if it is safe or not

[go-jet]

Yes, it is sql-injection safe in regard to passed arguments, but when you are using raw sql with string concatenation there are other ways to introduce sql-injection. For MySQL, named arguments are converted to mysql format ? before execution. You can see exact mysql statement and arguments with stmt.Sql().

[developer2346]

Hi How can I insert columns dynamically? I tried:

cols := []jet.Column{}
com := model.Comment{}

if comment.HTMLContent != "" {
  cols = append(cols, Comment.ContentHTML)
  com.ContentHTML = comment.HTMLContent
}
if comment.Approved != nil {
  cols = append(cols, Comment.Approved)
  com.Approved = *comment.Approved
}

stmt := Comment.INSERT(cols...).MODEL(com)

but cannot import github.com/go-jet/jet/v2/internal/jet so I cannot use cols

[houten11]

Use ColumnList instead: https://github.com/go-jet/jet/wiki/INSERT

[developer2346]

Sorry to ask so many questions. I am struggling with another problem

I need to add order clause dynamically. this is what I have tried:

for _, v := range Article.AS("a.article").AllColumns {
  if columnNameToOrderWith == v.Name() {
    if sort == "ASC" {
        stmt.ORDER_BY(v.ASC())
    } else if sort == "DESC" {
        stmt.ORDER_BY(v.DESC())
    } else {
         stmt.ORDER_BY(v)
    }
  }
}

the SQL result is ...ORDER BY `a.article`.title ASC; which results in jet: Error 1054: Unknown column 'a.article.title' in 'order clause'

I have aliased the article so I cannot use simply article. From what I have tested manually, it seems the correct syntax should be like: ...ORDER BY a.`article.title` ASC;

[go-jet]

What does a. in "a.article" represents? If a is schema/database, check wiki. Table alias can't contain ., because it is used as separator to specify struct destination field - <dest type>.<field name>.

AArticle := Article.FromSchema("a")  // use different schema(postgres) or database(mysql)
AArticle := Article.As("a_article")  // or alias table, but without .

// separate clause constructions from main statement
var orderBy []OrderByClause
for _, column := range AArticle.AllColumns { 
    ...
    orderBy = append(orderBy, column.ASC())
    ...
}

stmt := SELECT(
    AArticle.AllColumns,
).FROM(
    AArticle,
).ORDER_BY(
    orderBy...,
)
 

[developer2346]

It didn't work, Here is my query built with Jet:

SELECT 
     a.`article.id` AS "article.id",
     a.`article.uid` AS "article.uid",
     a.`article.title` AS "article.title",
     a.`article.admin_id` AS "article.admin_id",
     a.`article.category_id` AS "article.category_id",
     a.`article.section_id` AS "article.section_id",
     // used 'a' alias

     category.id AS "category.id",
     category.value AS "category.value",
     category.slug AS "category.slug",
     section.id AS "section.id",
     section.title AS "section.title",
     section.location AS "section.location",
     tag.id AS "tag.id",
     tag.value AS "tag.value",
     tag.slug AS "tag.slug",
     tag.`index` AS "tag.index",
     article_tag.id AS "article_tag.id",
     article_tag.tag_id AS "article_tag.tag_id",
     article_tag.article_uid AS "article_tag.article_uid",
     user.id AS "user.id",
     user.username AS "user.username",
FROM (
          SELECT article.id AS "article.id",
               article.uid AS "article.uid",
               article.title AS "article.title",
               article.admin_id AS "article.admin_id",
               article.category_id AS "article.category_id",
               article.section_id AS "article.section_id"
          FROM test.article
          WHERE TRUE
          LIMIT 10
          OFFSET 0
     ) AS a   // aliased here
     LEFT JOIN test.article_tag ON (a.`article.uid` = article_tag.article_uid)
     LEFT JOIN test.tag ON (article_tag.tag_id = tag.id)
     INNER JOIN test.category ON (a.`article.category_id` = category.id)
     INNER JOIN test.section ON (a.`article.section_id` = section.id)
     INNER JOIN test.user ON (a.`article.admin_id` = user.id)
WHERE TRUE
ORDER BY `a.article`.title ASC; // bad column name generated by Jet

[go-jet]

Ok, so a is the name of the sub-query. To export a column from the sub-query use From method.

aTitle := Article.Title.From(a)

Check the wiki - https://github.com/go-jet/jet/wiki/Subquery

[developer2346]

Yes but how to do that dynamically for all fields

[go-jet]

Aha, I see what you mean. If you iterate columns from .AllCoumns there is no From method. Use this workaround, until this is fixed:

var orderBy []OrderByClause

for _, column := range Article.AllColumns {
	if columnNameToOrderWith == column.Name() {
		exportedColumn := StringColumn(column.TableName() + "." + column.Name()).From(a)
		if sort == "ASC" {
			orderBy = append(orderBy, exportedColumn.ASC())
		}
		...
	}
	...
}