query not returning all requested fields

[JuiceyBrucey]

Hi,
I have attached the API XML.
I have looked at autoquery and it does the job for some of my questions, however I have not been able to find info on specifying table joins from the URL.
So I have used plain SQL.
SELECT
main.LastSavedDateTime,
main.InventoryID,
main.PartNo,
main.Description,
main.Status,
main.DefaultPrice,
main.RRPPrice,
main.Aux2,
main.BackOrderable,

price.InventoryID,
price.Price1,
price.Price2,
price.Price3,
price.Price4,
price.Price5

FROM IN_Main main
LEFT JOIN
IN_SellingPrices price
ON
price.InventoryID=main.InventoryID
ORDER BY main.InventoryID
OFFSET @StartPosition
ROWS FETCH NEXT @NumberRows
ROWS ONLY;

The problem is, it only returns the table fields from the IN_Main table, not from the IN_SellingPrices table. I need all of the fields requested in the SQL.
How can I get these fields returned as well?
Cheers

[Mike.Sheen]

Moved to REST API forum from SQL Server forum.

[Mike.Sheen]

Because you're now returning something which isn't a list of IN_Main, you just need to create your own custom class with the properties matching the SQL columns.

Import the attached plugin (Open Plugin maintenance and select Import XML from the Utilities tab of the ribbon), save and restart your service - it should be as you expect now.

Mike

[JuiceyBrucey]

Perfect. Thanks so much for that.
From what you have seen me ask, what do you think I should be focusing on as far as tutorials and learning?
I need to get on top of this so that I am not constantly asking questions.
Cheers

[Mike.Sheen]

From what you have seen me ask, what do you think I should be focusing on as far as tutorials and learning?
I need to get on top of this so that I am not constantly asking questions.

It's hard to answer this without knowing what you are trying to achieve - which would infer would you would need to know - and also what you already know.

You've achieved creating a custom route which returns a custom shaped result set which accepts parameters - for REST API development that's going to be a rather large part of any project - so at least that's out of the way!

As I think you're building a web store integration, the remaining aspect typically is creating and updating sales orders in Jiwa - and we already have comprehensive coverage in our API for that out of the box - so it doesn't leave much left for you to worry about.

Perhaps I can better answer your question if you list off brief bullet points on what you need to achieve.

[JuiceyBrucey]

Hi,
Yes, I am building an integration between JIWA and a custom CMS. So anything related to that is important.
My current abilities are mostly related to PHP, MySQL, HTML, Javascript, REGEX and other minor stuff. I build custom content managements systems from the ground up. It is a niche that I have cracked.
But I really like the idea of being able to make integrations between JIWA and my CMS. I would at a later date like to build desktop apps that integrate with my CMS.
But I have a long way to go with that.

So just on the last issue discussed, and particularly this part:
#region "Responses"
[Serializable()]
public class InventoryLimitFieldsResponse : List<MyCustomModel>
{
}
#endregion

#region "Models"
public class MyCustomModel
{
public DateTime LastSavedDateTime { get; set; }
public string InventoryID { get; set; }
public string PartNo { get; set; }
public string Description { get; set; }
public int Status { get; set; }
public decimal DefaultPrice { get; set; }
public decimal RRPPrice { get; set; }
public string Aux2 { get; set; }
public bool BackOrderable { get; set; }
public decimal Price1 { get; set; }
public decimal Price2 { get; set; }
public decimal Price3 { get; set; }
public decimal Price4 { get; set; }
public decimal Price9 { get; set; }
}
#endregion

I need to know more about this. The how, the why, construction of this and how it relates to this:
Db.SqlList<MyCustomModel>
Should I focus on core C#, or ServiceStack, or ORMLite, or all three?
Which one would you prioritise for now?
Cheers

[Mike.Sheen]

Should I focus on core C#, or ServiceStack, or ORMLite, or all three?

All three, with C# as the priority - particularly the topic of generics.

[SBarnes]

This is a little bit old but I found it a good intro to ServiceStack

https://www.syncfusion.com/ebooks/servicestack