RSS Feed

Plugin Developer Discussion

It is entirely possible I have a bug somewhere in my code, but I am starting to suspect it might be in the API.

Symptom: If I use a CBugEventQuery and add a join to a custom table, it complains that I didn't add a column from my custom table in the join expression even if I did.


Sample Code:
string ApiCustomTableName = (api.Database.PluginTableName("MyCustomTable"));
CBugEventQuery Q = api.BugEvent.NewBugEventQuery();       
Q.AddInnerJoin(ApiCustomTableName, "ixBugEvent=" + ApiCustomTableName + ".ixBugEventID");


Error message
FogCreek.FogBugz.Exception.FogBugzException: Invalid syntax: JOIN ON clause must include one column from each side of the join (Plugin_11_MyCustomTable not referenced)

It looks like we're printing out the wrong thing in the error.  Try

Q.AddInnerJoin(ApiCustomTableName, "BugEvent.ixBugEvent=" + ApiCustomTableName + ".ixBugEventID");

I've fixed the error reporting.  The error message you should be seeing is:

Invalid syntax: JOIN ON clause must include one column from each side of the join (must explicitly reference one of the following: Bug, BugEvent)

You should be able to fix this with the solution above.

That fixed it, but introduced two issues:

1) I was trying to use CBugEventQuery to avoid hardcoding the name of the FB table BugEvents. This sort of eliminates that benefit.

2) A new error "This query is unsafe because it references one or more FogBugz tables. To run this query, set IgnorePermissions to true" Sure I could turn on ignore-permissions, but then what was the point of using the CBugEventQuery object instead of a regular query object?

1) Sorry, hard-coding table names and columns is pretty much a necessity in version 1 of the API.  If it's any consolation, we will never rename a core FogBugz table without plenty of warning as it will be considered a breaking change in the API.

2) GetDataSet is always considered to be an unsafe method, because it returns raw SQL results.  The benefits of using the C*Entity*Query classes are (a) by default they remove records that are not visible to the current user, and (b) the List() method returns an array of objects (rather than a raw dataset) which each know and enforce their own permissions.

If you use List, you can access your JOINed fields by using bugevent.QueryField, however you will still have to set bugevent.IgnorePermissions = true in order to access that field.

For more information on permissions, take a look at this article: W129

As I said before, there was nothing wrong with the way you were doing it with a plain CSelectQuery.  I would recommend using CBugEventQuery only if you are actually trying to list BugEvents.  If you are just trying to list data from your table and JOIN on BugEvent so you can filter on ixBug, a CSelectQuery is probably the way to go.