Servoy Query Builder – Database independent queries
Servoy Query Builder – Database independent queries
All right, thanks, Steve. Is my screen visible to you? It is to me, yes. All righty. Yeah, so we are number 27 in the webinar series. Again, we like to do a mix of topics. There’s a lot of announcements that come up in between those announcements. We like to fit in some best practice webinars. Those are usually quite popular. And so today we’ll be one of those. And like Steve, one of my favorite tools is also the query builder, because I’m not so good at writing SQL. So this is going to be one of those webinars where there are some demos, but they’re not super impressive. They’re more use cases. And then we’ll really get into the how-to part. This is more of a how-to kind of webinar. So there will be a bit of slides after the demos. But in the spirit of the tech webinar series, we always demo before we give you guys slide. So it gives you a chance to watch something before you fall asleep. So we’ll go through a couple of use cases here in the demo. I’ll take you into the code of each one. But just overall, the reason I picked this topic is that I find that two things. One is that people seem reticent to use the query builder. I think there’s a combination of the intimidation factor. They didn’t really know quite what it was for. But it’s a lot easier than you think and it’s way easier than doing raw SQL. So we’re going to break it down. And the other thing I see is that people have some challenges using it as well. I think it has to do with maybe the syntax, there’s the call chaining, the fluent API can trip some people up as well. So we’ll take a look at that to make sure we resolve any of those questions. But let’s dive right into demonstration here. So the first example that I want to go through is just really a custom value list. In this case, we let’s just say we want to have a list of addresses from a table. In this case, it’s just example data customers. And we want to we want to format them or do something to them. I’m doing something simple here, which is just to concatenate those addresses, the different fields of the address, address, city and country and put them together in a list here so that I can I can choose say a shipping address or something from from a list. Pretty straightforward use case. A lot of times this gets hooked up to a global method value list. It doesn’t have to be you can also programmatically add those items to the list. That’s not so important, but let’s jump into the query builder side of things. So we have here. This is actually the wrong one here. Demo for I think low value list. All right. So the first thing we always do is we create the query object. And we’re going to go through the different parts of the query in when we wrap up in the overview, but for the value list, we’re really concerned with the result portion of the query. And as you know, a value list always has display values and optionally you can have real values if you’re storing a primary key by showing a display field. So in this case, our display value is really that concatenation of address, city and country. Now in this example, one of the things that I wanted to show is that when you when you are working with the result part of the query, you can add columns, but you can also add other things like functions, aggregates, even subquearies or literal values. So this is one case where instead of adding just a column, we’re actually adding the results of a function. And you can see that we call the concaten function, which is available on any on any QB column. So again, we put this in the result portion of the query and we add and then you can see you get code complete for really any of the functions that you might want to call on a column. We chose concaten, but you could pick something which is absolute value of math or something like that. So this is a real common use case that we have to create a custom value list. When you’re making a value list, you might also want to do a sort so that they show up in a particular order, not the order of primary key or something. So we use the sort part of the query. And again, we just add conditions to the sort part. And really, what you’re adding to a sort will be pretty simple. It’ll be one of these query builder columns and you’ll just add the ascending or descending switch to it. So at the end of any QB column, you can do ascending or descending and that will indicate which order that you want to do the sorting on. It does matter what order you add to the sort. This will sort first by address then city, then country if the addresses happen to be the same. It’s worth pointing out to that you could do, you can’t just sort on columns. You could also sort on on related columns through joins and we’ll get into joins in a little bit. The way that I’m loading the data is by calling get data set by query. Get data set by query takes the query object plus an optional limit. So you can limit the results. In this case, I put negative one to indicate that it will be unlimited. I want to get all results in the query. So you want to be careful with that if you have a really large result. In this case, I’m just calling set value list items with the data set directly on the element. I could also do application.set value list items, which we’ll sort of do it globally. Anywhere that that’s being used, I could also put this in a method which is hooked up to a global method driven value list so that it’s called sort of automatically any time the value list needs to be refreshed. So that’s one of the most common places where you’ll find this kind of query. Okay, so that’s example one. The things I want you to remember is that you can put functions in the result and how to add things to the sort. Something you’re noting is on the line where you have print SQL, I find it very helpful. You can use the database manager call getSQL to actually put that in an application output and check your actual SQL before you in the console. There’s also get SQL parameters which will send your parameters as an array also to the console. I find that very helpful when I’m building the query if I need to use Microsoft SQL Studio Manager or something to check my query or to make fine tune it. It’s really nice to be able to see that as an actual query straight. Yeah, thanks for pointing that out. I was planning to mention that. I use it all the time in debugger too when I’m just looking at stuff I print out what the query is of a query object or a found set query. In this case the reason I was storing that to a form variable that I could put right on the form here. So every one of these examples I’m printing to the screen what the underlying SQL is. And I think it’s a nice reminder of the SQL that I didn’t have to write when I see that because it gets pretty long pretty quickly. This is just a simple example. And one thing also to note is that the query builder takes care of protecting the parameters that you’re sending. You see the question marks. Those are the placeholders for the actual parameters. So you don’t have to worry about in the old days when you were writing your own SQL. And then you wanted to pass parameters as an array. You would have to remember well did I put all these parameters in the correct order and now SQL builder takes care of the query builder takes care of that. Yeah, that’s right. Another example here, this one gets a little bit more complex. I’m going to do a, this is for sort of searching and loading records. So I spoiled the surprise because I already had some stuff up here. But in this case, let’s say I just want to do a tech search and I want to be able to put in some words. And the reason I use this example is I want to get into nesting of logical conditions like wars and aunts because this is one of the places where I see the most difficult to you with the query builder. And I want to try to simplify it for you. So in my use case here, I want to, I have a grid. It’s just showing customers and I want to be able to search those customers based on the name, the city, the region country. I also want to, I want to make sure that any of the words that I put in my search, all of the words have to match, but it can match any of the columns that you see there. So that’s where all of the words have to match is the and and the any of the columns have to match is the or and we’ll take a look at the result. But let’s first put in a country like UK. We can see that there’s various customers there. I’ll put in London. Still, I’ve narrowed it down, but there’s still some, some more customers and I could put in something like north. And now I’ve narrowed it down to one customer. You can see that the sequel is changing down here as I was putting in my, my additional criteria. And it’s, it’s building that dynamically. So let’s look at the that example. I think it’s this one here. So again, this is a query based on the customer’s table. I’m only putting the primary key in the result. And that’s because I’m going to load it right into the found set and we’ll talk about that in a minute. The most important part of this query is the where clause. So really when you get into building the where clause, it’s quite common that you get into some logical conditions as well as as well as some functions. So in this case, the reason it’s dynamic is because I don’t know how many words the the user’s going to type and all of the words like an internet search, all of the words should show up in the search results. It’s not like it’ll get, you know, three out of four of them or something. So I’m iterating over the words and I’m converting each word into sort of an uppercase. It’s going to be used in a like function. So it’s the uppercase version of the word and it’s surrounded by the wild cards, indicate the contains. So we’re going to match on that. So here’s the here’s the part that I find trips people up and that’s that we’re going to nest the ores in the aunts. So really what we want here is we want to have a series of ores on the inside and then those will be joined together with a series of aunts. So any of these columns can match the word but all of the words must match any of the columns. So we’re going to have these four items in an ores and for every word we’re going to separate it with an and and if you flip back to the view here it’s kind of hard to read but you can see that it’s going with the four ores and then we get the and in between another group of four ores and then it’s plugging in just those words in the arguments and as Steve pointed out it would get pretty cumbersome to build up a list of arguments and keep it all straight and this really only took a couple minutes for me to write this query. So another thing that I see tripping people up is they think that if you do q dot or dot add you’re automatically modifying the original query in the where clause. This is the most common mistake I see and this is the mistake that I made when I first saw query builder. It wasn’t clear to me when I’m actually changing the query and so what I would point out is that the only time the query is modified in this whole method or at least the where clause is here when we add in the the and condition. That’s the only part that q dot where dot add. Anything else is just generating these little conditions. So for example when I call q dot or that returns a new or condition every time. It doesn’t do anything to the query it just gives me a little building block that I can stick stuff in and stash away and then put it back in later. So one of the ways I would say to simplify when you’re working on the logical hands and doors and nesting is to until you get the hang of it skip the fluent part you can do you can chain this all together in one call and do q dot or dot add you know dot add add you know dot and dot add and on. What I would do is separate it out you can see that every time I pass through I generate a new or is a local variable inside that loop. So that helps to keep it straight and then I can just add the stuff in there and then at the end I’ve also cached this as a local variable as it’s own and I’m adding to that every time and sticking it in the end. Again we’re printing out the SQL this time I’ve included the SQL parameters and finally what I would point out is that in the prior example we were loading records into a JAS data set which is a very simple in memory grid object but in this case we want to load it right into the found set which is bound to the form that we’re looking at so all the data bound stuff on the form reflects the results of the query so in this case we call found set dot load records and we just pass in the query object that’s all that we need. Keep in mind when you do that the result of the query should just contain the primary key if you add a bunch of stuff to the result it’s not going to work when you try to pass that in because when a found set is loading it just wants the primary key. You can play with the sort though if you apply a complicated sort and you want to include that in your found set loading that will work so I could have done that here. Okay that’s the second example. The third example is deals less with the where clause and more with the select part the result of the query let’s say that we want to get into doing some aggregates and this is really I think where the query builder comes into power because if any of you have worked with Servoyfor a long time and remember find mode I could have actually done this whole search in find mode but when I want to get into using aggregates then it’s a bit more complicated. The way I would have done this you know if I wasn’t thinking about performance is I would have made item count and order total as calculations and loading those calculations in this grid would have produced a lot of queries because these are these are bringing in related data and then and then summarizing it. So again every every line on this is just an order record but it’s going to give us the number of items in the order and then it’s it’s summarizing the total of the order which goes through every item and multiplies the quantity by the unit price. So you can see down here that we’re calling some and then it’s doing the quantity times the unit price. So let’s take a look at how we do aggregates in the query builder. When the form loads I call this load aggregates method again I start with a query from the orders data source and here we’re only concerned primarily with the result portion of the query. So the first thing I do is I add the primary key it’s kind of convenient you can just call add bk on the result you don’t need to to know the which column is the primary key or which columns because it would support composite key. And then here we’re going to deal with a couple of things one we’re going to introduce using joins and the second one is the is the actual aggregates. So in this case if I want to if I want to show the company name I’m going to bring in related data also if I want to aggregate the line items the order details I want to bring in related data. One of the the nicest things about the query builder is that if you have relations established then you will get code complete on all of your relations. So if I give myself q dot joins I get all of the relations that start from this data source and if I if I go say to order details I can again bring up my joins on the order details data source and say I could go over to products and over to suppliers you get the point. So it’s already using the conditions of those relations so that the relation items you know the the the two columns or the multiple columns and the operator will will automatically be and the join type will automatically be fed into that building that join. So you don’t have to build that join you can build joins if you want we’ll talk about that in a minute. So after we add or after we set up our joins let’s take a look at the aggregate itself basically any column will have and we’ll look at code complete we’ll have the aggregates in them and there’s there’s several there’s you know min max average sum and count. So any of these you just have to go to the column and reference it. One thing that I think is is really really nice is that it’s not just on the static columns that are in your database you can also put aggregates on on the results of a function. So we saw functions in the first example we’re bringing that back here and we’re calling on the related quantity we’re multiplying it by the unit price and then we’re summarizing the result of that. So we’re calling an aggregate on a function to get the order total. The item count as is a simple column aggregate this is an aggregate on something which is calculated dynamically in the query. So one thing also that I want to point out is that if you’re putting aggregates in your result then anything which is not an aggregate aggregate has to be grouped and that’s just a SQL thing because an aggregate is it’s an aggregate so it needs to be summarized by something. So that’s why we’ve put in here the customer and the order date because an order can be you know to the same customer but on different dates. Finally what I would point out in this example different from the first and the second example is that there’s a third way that we can load the results of the query and this is directly to an in memory data source. I don’t want to get into too much details about in memory data source because we just did a whole webinar on that a couple I think four weeks ago. So you can go back and watch that webinar if you missed it but this is sort of the query builder side of that. So at the end of this I could just feed all of the results of this query into a data source. In this case I’ve set up that data source in advance and it’s bound of the form that we’re looking at. But point out that you can also limit the results so if you want to just truncate the results you can do that. So again this is a form which is bound to an in memory data source which already has the schema defined for these four columns and that’s how we’re able to get that. This is way faster, way better performing than if we did calculations and it gives us things like the ability to sort on the aggregates. So I can know right away that this order was the one that had the most items or this orders the one that has the highest value. Okay moving along I have another aggregates example. Product aggregates it’s basically the same. It’s this time we’re looking at individual products and seeing the aggregates for them. So we can know which product by sorting had the total gross and sales which one we sold the most units of and also which one has been ordered the most recently. So this is basically the same as the one we just showed. If I look at the load aggregates method of this one which is also called on load it looks very familiar so I’m not going to go over the different individual parts. The only thing that I would point out is that I added one more aggregate because we didn’t have an example of min or max. I added one more aggregate here on the order date. And so you can see that I joined my way all the way over to the order from the product. So all the places where this product has appeared on an order and then I get the order date and then I get the max. So that now I can see the max and when you talk about dates is basically the most recent. That’s when I can find out when that product was most recently ordered. The last example I want to give is hopefully the most fun one because when we want to deal with aggregates we might want to deal with visualizations so I made a little sales analysis dashboard. And this is to just talk a bit about grouping. The other ones we did grouping because we have to because it’s in we use aggregates and we want to have the things we are aggregating by. Here we actually want to visualize by the groups. And also I bring in the limit into play here because I want to limit it to the top five. So we do top five units sold in categories in products and the top five units sold by suppliers. So you can see that these match up. If I look at product aggregates and I look at total units sold my number one seller is CHI with about 3800 orders and if I go to the sales I should see the same. Yeah, CHI is the biggest slice in the pie. So let’s take a look under the hood here. You can see down here this is actually three different queries to support the three different charts. So in this case I start with the query based on the order details. And what I do is I set out the aggregate right away because I’m going to reuse it. So that’s one thing I want to show you is that if you get into something where it’s a bit more programmatic, a bit more coding, you can you can always store you don’t have to add things into the query right away. You could always just create it and then hang onto it. In this case I’m going to reuse that aggregate three times in three different versions of the query. So I just go ahead and I summarize the quantity that gives me the total units and then I can reuse that aggregate in whatever context I want. The same goes with the sort because we want to do the top five. I want to sort by the aggregate descending. So that’s another thing you can sort by. We showed already sorting by a function. Now we can sort by by an aggregate. So here we’re going to we’re going to start to the first by the product. Again I’m going to set out the the the related column through that join sort of in advance because I’m going to reuse it in several places. I’m going to add to the results. So we’re just going to add the product and then the aggregate and then I’m going to add the product to the group by and then I just run it and I feed that data set into my chart. I don’t want to get into the details of how to render the chart but I’m just passing in the data set and it renders it pretty easily. Then what I do is I reuse the query. I just make a reference to my supplier related column and I just I call clear on the result and then I just add supplier and and the aggregate back in. So I’m reusing that aggregate here is just in a different context because I’ve cleared the group and I add the supplier to the group. So now I’m just summarizing by supplier. So the same aggregate three different ways and again I just print out this equal and add it to a different chart and I do the same thing down here with the category. I create the category relation and then I add it to the result and I add it to the group by clearing both of those first. So I’m reusing parts of the query here just to make things a bit easier programming wise. So I just wanted to show you this example. Number one to focus a bit more on on group being as opposed to the aggregate itself and also to show how you can reuse parts of the query. You don’t have to just do it all in one big chain call where you’re adding everything into the query. Finally I would just point out that every time I load the data set in each one of these I’m passing in the limit. We’re doing top five in those charts so I’m just saying what the limit is top five I could change that easily and even make a dynamic and something a user could choose. So that’s a good one to end on because we see people spend a lot of time building screens like this and I built this this morning before the webinar just about an hour ago. So it’s a way to make this a lot simpler. We’re running a bit out of time. I do have a lot of slides for review not a lot but more than usual because usually we focus on the demo but I just want to recap what we saw because there’s a lot of information and we’ll leave time for some questions. So again why would you use the query builder API? You should not be writing handwritten SQL. It’s error prone. It’s not very productive unless you’re SQL guru so for me it’s incredibly productive because I stink at writing SQL. But a couple other things. One is that the query builder generates cross database compatible SQL. So we’re not doing stuff that will work on SQL Server but not on MySQL or something like that. The other thing is that it’s strongly typed. So you saw that I get code complete on all the stuff from my data model. That’s hugely productive. You also get programmatic manipulation so I can sort of store stuff and come back and later reuse it and so you saw examples of that. That’s hard to do if you’re just like parsing strings. Another thing that we didn’t really point out but I just sort of take for granted is that it handles data types. Like I could pass a date into the where clause. I don’t have to like format a date string and say where you know order date is greater than and then put in a format a date string and hope that I didn’t mess it up which I would. I can just pass in a JavaScript date and let the query builder engine just handle it for me. So the same goes for strings and numbers. It’s quite nice. There’s several key parts of the query and I think once you learn these it gets a lesson intimidating. So there’s the result and remember that you can put lots of different things in the result. And so I’m going to call them of course but also aggregates the results of functions. We didn’t do a sub query example. I thought long and hard about a good example but I didn’t come up with one that I really liked but I do have a slide about that. You can also plug in literal values. So if you just want to do select you know 100 or select 1 plus 2 you could you could put that stuff in. Sometimes that’s needed and that’s quite easy to do. There’s the join part. The group by part. Here the sword. We didn’t talk about the having but the having part of the queries needed if you want to use aggregates and where clause. So adding columns we saw is pretty easy. You just add it. You can reference the column. If your query object is strongly typed you can also alias it which is sometimes needed because you might do two different versions of that column in different places for different reasons. It’s also easy to add the primary key without knowing what it’s called. I said you could add literal values. This is what it looks like. You just call the add value function of the result. You can also alias that of course too. Adding functions is one of the primary reasons people choose to use the query builder. So we saw the concatenation example. You can get a bit more fancy. You can mix and match functions. I could have done coalesce which will do sort of a default value if I get a null. So if any one of those fields were we’re null. I could have just done a blank space. You can add aggregates to the result as well. So we saw how to do that. Again, you can always alias stuff when you’re adding it to the result. And we also saw an example where we did an aggregate of a function. So you can string this stuff together and call it one thing in the result. Just one thing to note when you are doing aggregates you always got to put the non-aggregate stuff in a group by otherwise it will give you an error. We didn’t do an example of sub queries especially in the result. That’s pretty uncommon but you can add the results of a sub query to the result. When you do that it just has to have only one result in the one resulting field in the sub query. Building conditions in the where clause is pretty straightforward except for when you get into the nested conditions. But it’s pretty much you do the where and then you do dot add and then conditions are really, you can think of them as functions of the column itself. So you know company name dot equals product name dot up or phone dot not you know and then whatever condition comes after that. There are top level conditions as well so you can do q dot can top level functions. I mean sorry q dot functions dot and then you’ll get a list of functions where it’s not coming off of a primary column. So this is this is the format I usually use when I’m working with query builder. The API is fluent and by that I mean is that usually the methods of objects will just return the object again itself or the object that it created. So you can chain together this is how I would have written the nested or as an answer we saw before where I would do you know where dot add and then or dot add and then back to the end dot add. That can get really confusing so my advice is that you format it with indentations and that will keep the levels of the query how deep you are in the different conditions very clear to someone reading the code. But keep in mind you don’t have to use the fluent style. So when I’m working with customers a lot of times I get an email or phone call that I’ve been spending two hours on my query I don’t know why it’s not working and it’s really just a matter of the way the where clauses put together. So if you don’t want to use the fluent style you don’t have to just store things in variables and just remember that the only way to modify the query at least the where clause is by calling q dot where dot add. So q dot or dot add doesn’t do anything and it just generates a new condition which then you can put in inside another condition or add it directly to the where clause. You can do sub queries in the where clause I didn’t have an example but this is a real honorable mention because this is also pretty hard to do if you have to hand write SQL. So in this slide here we’re looking at a second query which will return us a list of customer IDs and then I use that in the where clause of the first query, the outer query. So I say where a customer ID is in and then the sub query. If you’re using the in operator you can also just pass in an array and that’s another place where it’s really nice that you just have an array of JavaScript stuff and the query builder formats it for you. If you’re new to query builder just remember you can use your relations. If you find yourself building your joins yourself you might be working too hard. It may be even appropriate to create a relation just to use in query builder. It’s not a bad thing to have a sort of statically defined object rather than dynamically making one. So you can reference these just on the joins of the query itself or any of the other cubie joins so you can chain them together. If you really need to build them you just I didn’t show an example of this but you can just do query.joins.add and then you have to specify the data source, the relation type and you can give it the alias and then you add the conditions. So you do the join.on and then you add the join on conditions and those will just be those conditions you know of equals or greater than or something. By the way if you’re ever working with part of the query builder API where it’s dynamic like this like we don’t know the I should say the ID doesn’t know by reading your code that is going to be based on order details you can just give it a type tag and then you’ll get code complete. So the next time you go to use that that join object you’ll get all the columns and all the related joins that you get in code complete. So make sure you take advantage of the JS docs. Sorting is really easy. You just do sort.add and remember that it matters what order you add them in and you can use related stuff as much as you want. So that’s that’s quite easy to do. Just keep in mind performance sorting is one of the things that can kill the performance of a query. So if you if you sort on something which is an index and you have a huge table you know what will happen. Not that often we see this but it really has its its use case if you want to do something where you’re you want to basically you want to an aggregate in the where clause that’s not legal in SQL you have to use the having clause. So if you’ve created an aggregate and you want to use it in a condition that condition can’t go in the where clause it has to go in the having clause it works just like the where clause you just do q dot having dot add and then your condition in this case I want to find the total units sold between one and two thousand. We saw three different ways that queries were loaded the simplest one is just to load it into a data set that just gives you that kind of in memory grid you can always limit those by passing in a limit. You can also just give a query directly to a found set and it’ll change its query and load those records in the found set doing limit in that case doesn’t really do anything as a found set already limits it to sort of its internal page size. Keep in mind that when you when you do load a found set based on a query the result portion of the query should only contain the primary key it doesn’t make sense to have other stuff in the result because the found set doesn’t care about that other stuff it just wants the primary key. Finally and probably most most powerfully you can create an in memory table based on the result of a query and we saw that in the aggregate examples I saw I showed you we were just pumping the results of that right into an in memory table which was already bound to design time stuff on a form so that can get you a lot of productivity. That concludes this I know I went over but it’s a pretty big topic I’ve been meaning to get this out to you guys for a while so I’m happy I had a chance to do so I saw some questions coming in throughout the talk. Steve yes I look we’ve got one question how to make data source flexible i.e. having a variable for example data in data sources dot dp dot and the dp server. I think what is asking is can you pass can you pass the names of either the server or the data source as a variable to the to the create select call right right yeah so you can do that it’s not in the create select because that’s already using this statically typed data source of you know whatever table it is if you go to the database manager API you can do database manager dot I think it’s create select I’m not sure and then you can pass in the data source name as an argument so if you don’t know it at design time but you’re going to know it at runtime then that’s what you do in fact that’s the way you used to have to do it before we added the data sources part to the solution model and giving us the statically typed query builder stuff we used to have to guess everything so it’s quite nice but if you can’t do that I think it’s database manager create select when you do that if you if you do know it at design time but for some reason you haven’t done the data sources approach remember to use the JSTOC to give a strong typing to your query builder objects the other question is is possible to get the SQL with the parameters already substituted I would like to get SQL command yeah I mean you can write a small routine no there’s not a call that supports that but basically you iterate over the arguments and then you just you can just you get first you call get SQL parameters you iterate over them and you just call string dot replace because that’ll just string dot replace just replaces the first occurrence so as opposed that you tills dot string replace which replaces all occurrences so if you do the JavaScript built in string dot replace so like they get SQL and then dot replace and then you just keep doing it until you’ve looped over all the arguments you could write a little routine and then use that anywhere you want that was it for questions I just wanted to make one comment about making your own joins without a relationship or even with relationship the thing that tripped me up for a long time was the the type of join if you’re using it through a relationship is going to be determined by the setting that you use for that relationship and the default I think is generally left out or join and so just be aware that when you’re when you’re using a relationship to make a join unless you’ve made a change to the default for that relationship it’s going to be a left outer join instead of an inner join which is commonly what I expected to be I think you can I’m not sure I guess could you change it like could you could you make a variable using joins dot the name of the relation and then change the I don’t know if you can change it after if you if you use one of the built-in ones that comes with code complete I think those are immutable but you although no maybe not maybe they just generate and return one which is mutable I never tried that the alternative is if you do a query dot joins dot add you can give it a string as the relation name okay and then it will and then you can do stuff to it so you could you should be able to do like get the set the join type it’s just join dot join type equals and then there’s a constant you pass in as a constant the constants for join types are under js relation or relations in the solution model