cannot convert symbolIDs["id"] (map index expression of type sq.CustomField) to sq.NumberField

[shaneHowearth]

Have got a sq.CustomField that's the id of a table, and I'm not allowed to convert it or compare it to the id field of another table.

Is there an easy way to convert a value, or do I need to (somehow) map all the fields in the CustomField to a NumberField mid-query?

[bokwoon95]

I don't understand, the signature for CustomField.Eq() accepts an interface{} so by right it should accept anything. Can you post the snippet of code you are doing?

[shaneHowearth]

Yeah - I ended up switching from sq.NumberField.Eq(sq.CustomField) to sq.CustomField.Eq(sq.NumberField)

[shaneHowearth]

This is what gets generated

WITH symbolIDs AS (
  	SELECT symbol_names.id, symbol_names.name, levenshtein(symbol_names.name, lower($1)) AS lev
  	FROM symbol_names 
  	WHERE symbol_names.name ILIKE $2
)
SELECT widgits.id, widgits.names
  	FROM public.widgits
  	JOIN symbolIDs 
  	ON :blank: = widgits.id 
  	GROUP BY widgits.id, widgits.names 
  	ORDER BY symbol_names.name DESC, MIN(symbolIDs.lev) 
  	LIMIT $3

From this

wt := tables.WIDGITS()
symbolNames := sq.From(wt).
	Select(wt.ID, sq.Fieldf("lower(unnest(?))", wt.NAMES).As("name")).
  	  	CTE("symbol_names")
  	symbolIDs := sq.From(symbolNames).
  	  	Select(symbolNames["id"], symbolNames["name"], sq.Fieldf("levenshtein(?, lower(?))", symbolNames["name"], q).As("lev")).
  	Where(sq.Predicatef("? ILIKE ?", symbolNames["name"], wildcardLikeQuery(q))).
  	  	CTE("symbolIDs")
  	query = sq.From(wt).
  	  	Join(symbolIDs, symbolIDs["id"].Eq(wt.ID)).
  	  	GroupBy(wt.ID, wt.NAMES).
  	  	OrderBy(symbolNames["name"].Desc(), sq.Min(symbolIDs["lev"])).
  	  	Selectx(
  	  	  	func(r *sq.Row) {
  	  	  	  	r.ScanInto(&sr.ID, wt.ID)
  	  	  	  	r.ScanArray(&sr.Names, wt.NAMES)
  	  	  	},
  	  	  	func() { result = append(result, sr) },
  	  	)

And this is what I am trying to get

WITH symbolIDs AS (
   SELECT
     id,
     LOWER(UNNEST(names)) AS name
   FROM public.Widgits),
 lev_dist AS (
   SELECT
     id,
     name,
 levenshtein(name, lower('cart')) as lev
 FROM symbolIDs WHERE name like 'cart%')
 SELECT
 widgits.id, widgits.names
 FROM public.widgits
 JOIN lev_dist ON widgits.id=lev_dist.id
 GROUP BY widgits.id, widgits.names
 ORDER BY COUNT(name) DESC, MIN(lev);

[shaneHowearth]

Almost there (Note the With, and the order of the CTEs listed appears to be important)

wt := tables.WIDGITS()
symbolNames := sq.From(wt).
   Select(wt.ID, sq.Fieldf("lower(unnest(?))", wt.NAMES).As("name")).
   CTE("symbol_names")
symbolIDs := sq.From(symbolNames).
   Select(symbolNames["id"], symbolNames["name"], sq.Fieldf("levenshtein(?, lower(?))", symbolNames["name"], q).As("lev")).
   Where(sq.Predicatef("? ILIKE ?", symbolNames["name"], wildcardLikeQuery(q))).
   CTE("symbolIDs")
query = sq.From(wt).
   With(symbolNames, symbolIDs).
   Join(symbolIDs, symbolIDs["id"].Eq(wt.ID)).
   GroupBy(wt.ID, wt.NAMES).
   OrderBy(symbolIDs["name"].Desc(), sq.Min(symbolIDs["lev"])).
   Selectx(
      func(r *sq.Row) {
         r.ScanInto(&sr.ID, wt.ID)
         r.ScanArray(&sr.Names, wt.NAMES)
      },
       func() { result = append(result, sr) },
   )

is emitting

WITH symbol_names AS (
    SELECT
    widgits.id,
    lower(unnest(widgits.names)) AS name
    FROM public.widgits),

symbolIDs AS (
    SELECT
    symbol_names.id,
    symbol_names.name,
    levenshtein(symbol_names.name, lower($1)) AS lev
    FROM symbol_names
    WHERE symbol_names.name ILIKE $2
)
    SELECT
    widgits.id, widgits.names
    FROM public.widgits
    JOIN symbolIDs ON :blank: = widgits.id
    GROUP BY widgits.id, widgits.names
    ORDER BY :blank:, MIN(symbolIDs.lev)
    LIMIT $3

Meaning that all I need to do is figure out how to access the symbolIDs.id for the JOIN and its name for the ORDER BY

Edit: (For the people that come after me) I have managed to expose the things that were being blanked by adding .As to the fields
eg

symbolIDs := sq.From(symbolNames).
   Select(
      symbolNames["id"].As("id"),
      symbolNames["name"].As("name"),
      sq.Fieldf("levenshtein(?, lower(?))", symbolNames["name"], q).As("lev")
).

And that solves that problem.

If you have a minute, I need to know how to get the COUNT(name) working, as COUNT takes no parameters

Alternatively, is there a way to add a Raw method that allows me to inject ANSI SQL if I need to, as a string

[bokwoon95]

I can't figure out why symbolNames["id"] is returning :blank:. :blank: means that the symbolNames CTE map doesn't actually have an "id" entry, and is returning an empty CustomField (an empty CustomField writes :blank: as its output). But I can clearly see you selecting symbolNames["id"] for the symbolIDs cte, which should result in a CTE entry keyed by "id". Can you print out the contents of the symbolNames map? With something like spew.Dump etc. It's great that you found a workaround in the meantime though.

sidenote: I dreadfully regret making CTEs and Subqueries just maps as the implementation is extremely dirty (I'm stuffing query metadata into the map as map entries) and also for stuff like this where CTE field lookups result in :blank:. I'm working on a v2 of this library which makes CTE fields look like cte.Field("name") instead which should be more straightforward.

I need to know how to get the COUNT(name) working, as COUNT takes no parameters

Yeah that's an oversight lol, I was seriously considering whether or not Count() should take a a Field parameter but I ended up not doing it because I thought COUNT(name) wasn't as popular as COUNT(*). Again, I plan to change this in v2 (into Count(Field) and CountStar()). In the meantime you can use sq.NumberFieldf("COUNT(?)", field) or just sq.NumberFieldf("COUNT(name)") directly.

[shaneHowearth]

But I can clearly see you selecting symbolNames["id"] for the symbolIDs cte, which should result in a CTE entry keyed by "id". Can you print out the contents of the symbolNames map? With something like spew.Dump etc. It's great that you found a workaround in the meantime though.

I honestly wouldn't know how to do that, I have a fmt.Println("%#v", query) at the end. but it's got quite a bit of .. cruft :)

In the meantime you can use sq.NumberFieldf("COUNT(?)", field) or just sq.NumberFieldf("COUNT(name)") directly.

What I've ended up doing is putting the COUNT() into the CTE

case SymbolSearchWidgit:
	wt := tables.WIDGITS()
	symbolNames := sq.From(wt).
		Select(
			wt.ID,
			sq.Fieldf("lower(unnest(?))", wt.NAMES).As("name"),
		).
		CTE("symbol_names")
	symbolIDs := sq.From(symbolNames).
		Select(
			sq.Count().As("count"),
			symbolNames["id"].As("id"),
			symbolNames["name"].As("name"),
			sq.Fieldf("levenshtein(?, lower(?))", symbolNames["name"], q).As("lev"),
		).
		GroupBy(
			symbolNames["id"],
			symbolNames["name"],
		).
		Where(
			sq.Predicatef("? ILIKE ?", symbolNames["name"], wildcardLikeQuery(q)),
		).
		CTE("symbolIDs")
	query = sq.From(wt).
		// Include both CTEs in the query (order in WITH clause matters!)
		With(
			symbolNames,
			symbolIDs,
		).
		Join(
			symbolIDs,
			symbolIDs["id"].Eq(wt.ID),
		).
		GroupBy(
			wt.ID,
			wt.NAMES,
			symbolIDs["count"],
		).
		OrderBy(
			symbolIDs["count"].Desc(),
			sq.Min(symbolIDs["lev"]),
		).
		Selectx(
			func(r *sq.Row) {
				r.ScanInto(&sr.ID, wt.ID)
				r.ScanArray(&sr.Names, wt.NAMES)
			},
			func() { result = append(result, sr) },
		)